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]

Regular 2nd shift 2pm - 10:30 pm
Late 2nd shift 2pm - 1am the next day, the shiftdate in this case would convert the date back one

Regular 3rd shift - 10pm - 6:30am
Odd 3rd shift 10pm - 11:59pm left early sick the off time shiftdate would be moved ahead one day from the date.

 

[jackd]

I find this line confusing
Odd 3rd shift

off time shiftdate would be moved ahead one day from the date

and

Late 2nd shift 2pm - 1am the next day, the shiftdate in this case would convert the date back one

I guess I'm asking What is the significance of the off time shift date? What does it mean?

 

[jcaptchaos2]

You have a start and stop time for each day, if a 2nd shift employee works from 2pm one date until 1am the next date the query is matching on and off times by date so it would match the 1am with the start time of the next day and it wouldn't have an off time to go with the 2pm start time on that date. Using shift date I can use the query to match on and off times by shift date. Every thing has been working fine the issue comes in with the primary key's with employee number and date, by using date if the employee works until 1am he already has an off time for that date and when he goes to leave at 10:30 pm it says you already have an off time and won't let him transact off. If I could make the primary key the employee number and shiftdate then the formula from the shift date would have moved it back one day making it work fine.

 

[jackd]

If you had called the field ShiftStartDate or DateToChargeTheShiftTo it would be OK?

The issue then, for shift 2, is to make sure the time from midnight to 2AM etc is not charged to the Date where the 2AM actually occurred. You want 1 day back.


If I start at 2PM Day X and work until 10:30 PM Day X, then
<b>DateToChargeTheShiftTo = Day X (8.5 hrs)</b>

If I start at 2 PM Day X and work until 1 AM Day X+1, then
<b>DateToChargeTheShiftTo = Day X (8.5 + 1.5[til midnight] + 1 in Day X+1) (11 hrs)</b>


For shift 3 10PM Day X to 6:30 AM Day X+1

If I start at 10PM and leave before midnight, say 1 hr 30 min, then
<b>DateToChargeTheShiftTo = Day X + 1 (1.5 hrs)</b>

It seems from this that
- if it's shift 2, use the date the shift started
- if it's shift 3, use the add 1 day to the date the shift started.

So, with some data,
Bob works shift2 on Jun 13 and works until 1AM Jun 14.
Jim works shift3 on Jun 12 and goes home at 11 PM Jun 12.

Bob
Shift 2
DateStart Jun13 2011 1400 (2PM)
DateFinished Jun14 2011 0100 (1AM)
Hours 11
DateToChargeTheShiftTo Jun13 2011

Jim
Shift 3
DateStart Jun 12 2011 2200 (10PM)
DateFinished Jun 12 2011 2300 (11PM)
Hours 1
DateToChargeTheShiftTo Jun 13 2011

For the PK you could use

EmployeeNumber + Shift + DateToChargeTheShiftTo

To determine the DateToChargeTheShiftTo
Iif(shift = 2, DateStart, iif (shift = 3, DateStart +1))

Is this helpful, or are we back where you started?

 

[xenou]

It seems to me a more robust design would allow an employee to have more than one shift in a day. To handle various situations such as: working two shifts, having to clock out and return later in the same day, working long hours of overtime crossing midnight, and so on. In other words, using a date or a shift as part of a primary key may continue to lead you into further problems.

 

[jackd]

xenou,

I don't disagree. I'm not very familiar with the situation and business rationale. I'm just trying to help. I don't like using a date in the PK, but by using shift and date, you could have 2 shifts on the same day.

Suggestions are welcome.

 

[jcaptchaos2]

Maybe, but at this time we are far enough along that I need to finish this and then I could start working on a second version. The problem I have is I was tasked with creating this and pretty much designed it with all of this boards help. It seems to be working on except when the above occurs. If someone has a generic db for this out there that I could build off of that would be great. Thanks

 

[jcaptchaos2]

The probability of someone working 2 shifts in one day would be very rare and if that happened I would just adjust their time to make sure they get paid the correct amount of hours.

 

[xenou]

Since (it seems) you can't use a calculated field in a primary key that option is off the table. How is the data being entered? Maybe the shift date can be updated when the record is saved rather than calculated in a calculated field. Also, I'd use the autonumber as the primary key and only use a unique index for the rest - it may simplify things later on when you do upgrade to Version 1.1 It sounds like you are using the primary key to guarantee uniqueness, and an index can do that.

(what was your conclusion to Jack's recent post).

 

[jackd]

Xenou,
That's what I was trying to suggest in post#2. But I wasn't aware of the calculated data type in 2010. I have tried to mock it up in Acc2003. I created a table PayRollTime
with fields

EmployeeId Number
Shift Number
DateStarted Date
DateFinished Date
Hours Number
TimeOff

and DateToChargeTheShiftTo Date

PK is EmployeeId + Shift + DateToChargeTheShiftTo

I have a form for ShiftStart where you pick an Employee from a combo; then a Shift from a combo; it defaults to Now for the DateStarted, but can be overridden. A txtbox on the form gets populated with the DateToChargeTheShiftTo (mm/dd/yyyy). There is a button to Add this shift record to the Table.

There is another form with subform.
Form is ShiftCompletion. It has a combo with a Distinct list of DateStarted (without tiime), there is a second combo for Shift.

You select a Date and a shift, that goes to the PayRollTime Table to get records for Employees with corresponding records.

Select the appropriate record and record the DateFinished. The fields already populated are locked to prevent changes to Shift, DateStarted etc. A txtbox on the subform automatically populates with the Hours for that Shift.
There is a button to Update and complete this Shift record for this Employee.

It's all in Acc2003, but seems to do what is needed. However, I don't know the application in any detail. I convinced myself that an autonumber PK and a unique Index could work.

I chose the PK I did based on JC's fields. It seems his choice of DateStarted as part of the PK could be the source of all his issues. I don't have Calculated data type, but have used a calculation when creating the Shift record, and storing the result in DateToChargeTheShiftTo.
I haven't tried to build any logic to prevent someone from "completing someone else's shift record".

I still don't understand the TimeOff field, but it could be processed with some code.

There is a business rule about booking Off when you have a 3rd shift going into StartDate +1 that has not been explained thoroughly.