IN ACCESS 2007 - Why is a field with an AutoNumber data type not a good candidate for the foreign key field for a one-to-many relationship?

---

Answer from Richard Rost:

It's not only a bad candidate, it's IMPOSSIBLE.

An Autonumber gets assigned by Access each time you add a new record to a table. So it's great for a PRIMARY key, like CustomerID in the CustomerT table:

ID, Name
1, Joe
2, Fred
3, Bill
etc.

However, if you have an OrderT table and you want to track which CUSTOMER each order is from, you can't have Access assign IDs automatically. You have SPECIFY which customer belongs to each order (the Foreign key).

The primary key of the ORDER table would be an Autonumber (the OrderID)… but the foreign key has to be a NUMBER of type Long Integer.

OrderT table:
ID, CustomerID, OrderDate, etc.
1, 3, 1/1/10
2, 1, 1/1/11
3, 3, 1/1/11
4, 2, 1/1/12
etc.

Here you can see that Order 1 was made by Customer 3 (Bill) and so on. If Access were assigning those values, it would make your data meaningless.

It sounds to me like you need to learn a little more about the basic concepts of building a Relational Database, and making relationships between two tables.

Watch this free tutorial on my web site for help with this topic:

http://599cd.com/tips/access/relational-tables?key=BlFAlEx

I also spend a whole hour in my Access 201 class going over table relationships:

http://599cd.com/site/access/access201?key=BlFAlEx