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]

Jack,

Thanks for your help, I am wondering if you could give me a little more detail with my info in it. Here is what I have for a table.

ID -- Autonumber
Employeenumber -- number - also one of the current primary keys
Time Off -- Date/Time
Date -- Date/Time -- At this time the other primary key
Shift -- Number
Shiftdate -- Calculated

Here is the issue I run into with this, if an employee who usually gets done at 10pm at night works until 1am they already have a time off for the next day so when they try and transact off the next day it says they already have a time off. That is why I created the shift date it contains a formula that looks at the time off and recognizes it to be between midnight and 6am and then moves the the shift date to the previous day. I guess I don't know where I would add the suggestion you gave me.

 

[jackd]

Thanks Bob for the info. I have Acc2003, and am not familiar with the new data types of 2010. I was not aware that there was a calculated field datatype. Typically when we talk about a calculated field in Access, it is reference to a calculation that can be determined as needed, and not necessary to store it.

As for the Docmd, you are no doubt aware that that code I showed was generated by Access (2003) when creating a Button's Save code. I couldn't agree more that its purpose isn't obvious nor helpful for documentation. I guess that's what you get when using Acc2003.

jcaptchaos2, sorry if the info was misleading. Certainly wasn't intended, and I now know that there is a datatype "Calculated" in Acc2010.

 

[jackd]

There is an interesting post re calculated fields here
http://allenbrowne.com/casu-14.html

 

[jcaptchaos2]

Thanks

 

[jcaptchaos2]

Ok that is interesting, my formula in this case is

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

My field name in my table is [shiftdate], code I put this some how in a text box on my form and have it update the table?

 

[jackd]

JC, I do not have acc2010 so have no ability to define or work with a Calculated data type.

I saw your table layout. Could you provide some sample data from such a table and we could try to work out a procedure? The ShiftDate field, which you have defined as Calculated data type will have to be changed to another data type. Also, could you describe exactly what each field means, and what the goal is?
I'm not clear on the Time Off field. I see it being something like

if I normally finish a shift at 10PM and work until 1AM, I have normal hours (say 8 hours) for Day X and overtime hours for Day X (2 hours [10 to 12PM) and Day X+1(1 hours[ midnight til 1AM]). If you show hours worked on Day X+ 1, that interferes with some other rule/regulation ????

How does Time Off <0.167 get figured in? It means something to you just as Time Off >0.75?
Perhaps you could describe this part a little more.

It does seem to me that there are some underlying business rules that are impacting/demanding a certain calculation.

 

[jcaptchaos2]

Thanks for the help,

Table name = parolltimeoff

field names, data type and descriptions
ID - autonumber - just a place holder
EmployeeNumber - Number - employee scans badge (Primary key) along with date
Time Off - Date/Time (short time) there is a formula on the before update on the form

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.Time_Off = Now
End Sub

Shift - Number - There is this formula in the after update event on the employeenumber text box

Private Sub Employee_Number_AfterUpdate()
shift = DLookup("[Shift]", "[employeetbl]", "[Employeenumber]= " & [Forms]![ParollTimeofffrm]![EmployeeNumber])
End Sub

Ok explaining the time off <.167 for 2nd shift and >.75 for 3rd shift.
I have created a field name of shiftdate to adjust the date someone works, for example 3rd shift since they start on a Sunday at 10pm but the pay date is really Monday I needed to add this so if they leave prior to midnight it forwards the paydate to the next day, for 2nd shift if they stay late I am using this formula to back the paydate up one day.
.167 = 4am
.75 = 6pm

Table names again
ID
Employeenumber
Time Off
Date
Shift
Shiftdate

 

[jackd]

What exactly is the reason to back the paydate up 1 day?

eg Company policy says you can't claim....????

And do you have some sample data?

 

[jcaptchaos2]

3rd shift starts at 10pm on lets say Sunday, in my timeon table different then the one we are talking about it has a shiftdate to move the pay date ahead one day so it matches with the off date, if the employee leaves before midnight it needs to move his off date forward to match

 

[jackd]

Can you describe an example with 2 or 3 employees where

- 1 works regular shift
- 1 works beyond his shift into next day
- 1 works regular but leaves early before midnight

Can you describe how each should be processed? What info do you need recorded and where?

I don't know your application nor your data. Just trying to see if there's a way to make something work to give what you need.