Benefit of using an AutoNumber field.

[ColdDay]

We are creating a new payroll system in Access 2010 and are having a discussion as to if it would be better to use the employee ID number as the PK and use it to relate to other tables or to create an Auto Number field and use it instead to relate. We need to know what, if any, benefits there would be to use the Auto Number field.

Benefits such as:
-increased speed for lookup, data entry, etc.?
-easier to relate tables?
-cascading updates, deletions?
-anything else?

The organization currently has about 500 employee records in the system with about 200 of them active. The other 300 have been terminated over the years, but the records still exist in the system.

Weekly time sheet records are entered manually (keyboard) and daily. We hope to be able to have the time sheet information eventually entered from the shop floor with hand held devices and then uploaded to the system at the end of the day.

Table name: BasicInfo.

Using EmplID as PK for relating tables. Set EmplID and SSN to Unique.

[tt]
EmpID LastName Firstname MI SSN Location
===== ========= ========= == ========= ========
1111 Employee First 111-11-1111 1
1222 Worker Second J 222-22-2222 2
1333 Rascal Third 333-33-3333 3
[/tt]


Use AutoNumber as PK for relating tables. Set EmplID and SSN to Unique.
[tt]
Auto# EmpID LastName Firstname MI SSN Location
====== ===== ========= ========= == ========= ========
1 1111 Employee First 111-11-1111 1
2 1222 Worker Second J 222-22-2222 2
3 1333 Rascal Third 333-33-3333 3
[/tt]

Some of the tables that BasicInfo will be related to will be for wages (each employee will have multiple rates), leave time balances and time sheet entry.

Please forgive the columns not perfectly aligned.

Any input will be greatly appreciated.

Thanks.

 

[PHV]

Provided that EmpID never changes for a given employee, I see NO benefit to use an AutoNumber as PK.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ColdDay]

PHV,

Thanks for reminding me that it is very possible that an individual could come back to work and for some unforseen reason we may need to issue them a new EmplID number. We try not to, but there have been times when we had to.

We'll discuss the issue some more.

Thanks.

 

[dhookom]

I took over a system where the primary key was the employeeID from PeopleSoft. A few years later, we replaced PeopleSoft with another HR system and had to change a lot of functionality.

I almost always recommend an autonumber since SQL Server has similar functionality with Identity columns.

Duane
Hook'D on Access
MS Access MVP

 

[ColdDay]

Duane,

A few ago I talked with some local SQL guys about this issue, but with SQL 2005. They said yes also.

I did not know, but it did seem to me that the logic would transfer to Access. I guess it just depends on the particular situation.

Thanks.

 

[BNPMike]

In relational databases, there is no verb 'relate'. If you want to get data from more than one table you 'join'.

A relation is a table - it creates a relationship in each row (tuple) between the fields (domains) of the table.

If you want to relate, go to the pub.

Or a chat line.