Primary Keys

[jcaptchaos2]

I have a tavle where I have the employee number and the date as primary keys which I though was working well. The table is for tracking the off time for a day. I have had to add a calculated field to the table which I call shift date as I had to add this formula to it.

IIf([shift]=2 And [Time Off]<0.167,[date]-1,IIf([shift]=3 And [Time Off]>0.75,[date]+1,[date]))

I would like to have that date be a primary key along with the employee number but you can"t have a calculated field as a primary key. The problem with the regular date being a primary key is that if a 2nd shift employee who usually gets done at 11pm works until 1am the next morning they already have a shift off for that day and when they try and shift off the next day it will not let them. Any way around this?

 

[jcaptchaos2]

Ok thanks for sticking with this let me try and clear some more of the questions up,
The data is entered into the table with 2 different forms the form names are "Parolltimeonfrm" and "Parolltimeofffrm" On the time on form there 3 text boxes and one combo box.
Text box one - "employeenumber" that is the name of the control source that is in the "parolltimeontbl" this is the only thing that the employee sees when the form is open and all they do is scan their employee badge and then they sre done.
Text box two - "Time On" is the control source name in the table this is a hidden txt box there is a before update event on the form that enters the data into the table

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.Time_On = Time
End Sub

Text box three - "Date" is the name of the control Source, this just has the =Date() as a default value.
Combo Box = "Shift" This is entered into the table with an after update event from the employee number text box

Private Sub EmployeeNumber_AfterUpdate()
shift = DLookup("shift", "employeetbl", "employeeNumber=" & EmployeeNumber)
End Sub

Both the on and off forms work about the same way and go into two different tables.

 

[boblarson]

One thing - Do NOT use DATE and TIME as field or object names. That will potentially cause problems throughout your database. You should rename them and all places they are referenced NOW or else you may end up with some bad stuff happening. And make sure Name AutoCorrect is off before you rename anything. Because with them being integral functions within Access, things could get really messed up if Name AutoCorrect starts doing things when you rename.

I can't stress how important this is as I've seen several people in the past who have had those names and them causing all sorts of problems.

 

[jcaptchaos2]

Ok I will try an explain the shiftdate field that is also in each table again,

3rd shifts normal work time is from 10pm one night to 6:30am the next morning, they start the week on Sunday night but the pay date is Monday. I am using the shiftdate calculated field to adjust the date as needed, for example if a 3rd shift person has a start time of 10:00pm the formula in the shiftdate field says to calculate the shiftdate as the timeon date +1 but if the person is late and the time on is after midnight the shiftdate does not move ahead one. On the off time the shiftdate field has a formula in it which states that if a 3rd shift person leaves prior to midnight calculate the shiftdate to be the date +1 and in the same formula 2nd shifts normal work time is 2pm - 10:30pm but if they stay late after midnight it calculates the shiftdate to be -1. There is a shift date in each table the on and off with a little different formula in each one.

 

[jcaptchaos2]

Bob, ok thanks for the advice, I am using Date as a name but not time as in the tables I use "Timeon" and "TimeOff" I will try re-naming the date.

 

[xenou]

Well, I never figured out if you mean by calculated field a real 2010 Access Calculated Field. But if the data entry is controlled by a form then you really should have no problems. Don't use a Calculated Field (if you are using one) but calculate the value to be placed in the field when the records is committed - this can be done in the form code.

My advice would be to use the shift number to identify the correct date for the payroll purposes - this is either the day when you start work or the date when you end work (according to your payroll rules).

It should be easy to determine a shift date based on the start time (which is going to be within a half hour to an hour of the planned shift start time). Since the shift date can be calculated from the time in and the shift number, you don't even need to calculate this at the time of the swipe (though you could). You can update it at any time or even when payroll is prepared - which is also a good time to run exception reports to identify any records that don't fit the expected assumptions. Again, I think it's a simple rule:

Shift 1 - time in's between (xx:xx:xx - .5 hr) and (xx:xx:xx + .5 hr) '//where xx:xx:xx is the scheduled shift start
Shift 2 - time in's between (yy:yy:yy - .5 hr) and (yy:yy:yy + .5 hr) '//where yy:yy:yy is the scheduled shift start
Shift 3 - time in's between (zz:zz:zz - .5 hr) and (zz:zz:zz + .5 hr) '//where zz:zz:zz is the scheduled shift start

 

[xenou]

Note that no matter what you do, I think it is simpler and best to design your swipe entry to only do the bare minimum:

collect the Employee ID and the date/time.

You can process this raw data through any kind of logic you need after that. If you build too much into your form you make the system less robust and harder to revise or improve. I'd take the daily data and run it into a series of queries to process it via the logic you need to break it into paydates and shifts the next day ... also you can view exceptions and have your payroll team fix them (such as people not swiped out or what have you). Save the original data as part of your audit trail and keep it safe and back it up.

 

[jcaptchaos2]

Ok thanks for the advice, I am on my home from work, I will look at changing the main table when I get home. Really appreciate the advice from this site and sticking with this post