Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Benefit of using an AutoNumber field.

Benefit of using an AutoNumber field.

Benefit of using an AutoNumber field.

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.

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

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

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

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.


RE: Benefit of using an AutoNumber field.


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.



RE: Benefit of using an AutoNumber field.

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.

Hook'D on Access
MS Access MVP

RE: Benefit of using an AutoNumber field.


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.



RE: Benefit of using an AutoNumber field.

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close