CODEWITHSUNDEEP
ms-access
newbieDB Builder
newbieDB Builder

Reputation: 75

Using a calculated field at table level

After Googling for a while I was wondering about the use of a calculated field at table level. I know its a big No,No but I wanted to use it as a method of preventing duplicates. For Example, I have a Payroll table and I wanted to concatenate the Employee ID field and the Work Day field. So during payroll entry some code can lookup that field to alert if the record exist.

Any advice is greatly appreciated.

Upvotes: 0

Views: 46

Answers (2)

SunKnight0
SunKnight0

Reputation: 3351

As long as your data entry is through a properly managed form and not a form directly linked to the table you don't need the lookup to be on a single field, you can check for duplicates that match multiple criteria.

Simple example:

If Nz(DLookup("MyTableID","MyTableName","EmployeeID=" & Me.EmployeeID & " AND WorkDay=#" & Me.WorkDay & "#"),0)=0 Then
    [Handle Add Record to Table]
  Else
    [Handle duplicate exists]
  End If

Upvotes: 1

Gord Thompson
Gord Thompson

Reputation: 123419

Calculated fields cannot be indexed, so any lookup on a calculated field will probably result in a table scan. You'd probably be better off to create a unique index on the two columns to prevent duplicates.

Upvotes: 3

Related Questions