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?

 

[jackd]

You could have an autonumber primary key, and then
create a Unique Index ( a compound Unique index) made up of
EmployeeNumber and ShiftDate ( ignore Nulls = No)

The database can work with the Autonumber PK.
The Unique index will handle the EmployeeNumber/ShiftDate combo.

I'm not sure about the business rules re

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

 

[jcaptchaos2]

Will give it a try thanks

 

[boblarson]

And here's how you create a multi-field index:
http://www.btabdevelopment.com/ts/mfi

 

[jcaptchaos2]

Thanks again Bob you have been a huge help

 

[jcaptchaos2]

Ok the instructions were great but it still won't let me add [shiftdate] using this, when clicking on the index button even though it is in the table it is not an option in the indexes table, maybe because it is a calculated field.

 

[boblarson]

Ok the instructions were great but it still won't let me add [shiftdate] using this, when clicking on the index button even though it is in the table it is not an option in the indexes table, maybe because it is a calculated field.

That would be correct. You can't index a calculated field. So it can't be part of a multi-field index and it can't be the primary key.

 

[jackd]

Perhaps it is possible to create a field in your table. Do you calculations as you see fit, and then store the result of your calculation into this new field. The field could be the one that you use with EmployeeNumber to create the compound Unique Index.

As long as your logic allows you to make the calculation based on existing fields, it seems that you could use this new field to store and retrieve data. And when you go to store a new value, you would do the calculation before storing. Access (Unique index) will prevent storing a duplicate, but you could check for an existing value of the calculation, before attempting to store a value.


Just a few thoughts for consideration.

Note: I haven't tried it, but after reading some responses, I'm looking for an option to get you what you're looking for.

 

[jackd]

I created a small table, form and query.
Table:

table_name		field_name		data_type	length	
captchaos		MyDate		Date	8	
captchaos		EmployeeNumber	Long	4	
captchaos		MyCalcField		Text	50	
captchaos		id		         Long	4

It seems to handle the MyCalc field, and the UniqueIndex

On a command button I have a calculation, not too elaborate, but just something to do a calculation.

Private Sub btnSave_Click()
On Error GoTo Err_btnSave_Click
Dim lCalc As Long
lCalc = Month(Me.MyDate) + 19 + Year(Me.MyDate)
Debug.Print lCalc
 Me.MyCalcField = Me.EmployeeNumber & CStr(lCalc)

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Saved record with " & Me.MyCalcField
Exit_btnSave_Click:
    Exit Sub

Err_btnSave_Click:
    MsgBox Err.Description
    Resume Exit_btnSave_Click
    
End Sub

Don't know if it helps, but thought I'd try something and pass it on.

Good luck.

 

[boblarson]

And Jack - do you know what a Calculated Field datatype is? (that is new in Access 2010). So it isn't quite the same as to what you have shown.

Also, you shouldn't be posting Docmd.DoMenuItem code. That is obsolete code which exists just for backwards compatibility and it should be converted to the DoCmd.RunCommand code instead. Not only is that the newer code to use but it also is better for self-documenting as it is clear what it does whereas the DoMenuItem code is not all that clear.