Working with AutoNumbers as foreign keys

I probably get more questions about this technique than any other, when people build their own databases. This lesson does require an understanding of primary keys, foreign keys, common fields, AutoNumbers and relationships. So if you're not sure what these terms mean, please watch those videos in the Access 2007 Essential Training and then return to this one. When you're relating two tables together, the usual method is to use the primary key of one table as the foreign key or common field in the second table. For example, let's open up our Customers table and our SalesReps table.

In the SalesReps table, each employee has his or her own ID number, a unique identifier that Access uses to make sure that the person isn't listed in the table twice. Now let's look at the ID field in Design View. Much of the time an AutoNumber data type is used for this primary key, which sequentially numbers the records as they are created, so that no two are given the same ID number. That includes this table, I'll right- click on the SalesReps tab and click Close. Let's scroll over to the right in the Customers table to see the SaleReps field.

When we get a new customer and we assign them a SalesRep, instead of re-keying all the employee's name and contact information every time, we just use their ID field. If you go to the Database tools ribbon and then click on the Relationships button, you can see that Access understands that there's a link between the SalesReps and the Customers, and that everyone SalesRep can have many Customers, as shown by the line with the one here and the infinity on the Customer side. When we later run reports, we'll use the query to bring the Customer information and the SalesRep information together into one place, using this relationship.

I'll Close this window. But where people get confused is that when they create the SalesRep field here in the Customers table, they assume that because it was an AutoNumber in the original table it has to be an AutoNumber here, but that won't work. The purpose of an AutoNumber is to automatically increment itself, that's not possible here, where I'm supposed to pick the SalesRep off of the list. So let's look at the Design View of the Customer table and click on the SalesRep field. Instead of an AutoNumber, this foreign key field must be a Number data type, and the field size should be Long Integer.

According to Access that's the only field type combination that will work to create this relationship. Setting the common field to Number and Long Integer in the foreign table will allow Access to match it to the AutoNumber in the primary key, allowing your database to merge the information properly.