Watching:

Working with AutoNumbers as foreign keys


show more Working with AutoNumbers as foreign keys provides you with in-depth training on Business. Taught by Alicia Katz Pollock as part of the Access 2010 Power Shortcuts show less
please wait ...

Working with AutoNumbers as foreign keys

I probably get more questions about this technique than any other when people build their databases. This lesson does require an understanding of primary keys, foreign keys, common fields, auto numbers and relationships. So if you are not sure what those terms mean, please first watch those videos in the Access 2010 Essential Training and then return to this one. When you are 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. For example, let's open up our Customers table and our SalesReps tables.

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 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. Let's close the SalesReps table and look back at Customers. I will scroll over to the right- hand side to look at SalesReps. When we get a new customer and assign them a sales rep, instead of re-keying all of the employees' name and contact information every time, we just use their ID field.

If you go to the Database tools Ribbon and then click on Relationships, you can see that Access understands that there is a link between the SalesReps Table and the Customers Table, and that every one SalesRep can have many customers as shown by the 1 and the Infinity on each side. When we later run reports, we will use a query to bring the Customer information and the SalesRep information together into one place using this relationship. I will 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 in auto number in the original table, it has to be in auto number here, but that won't work.

The purpose of an AutoNumber is to automatically increment itself, but that's not possible here where I picked the SalesReps off of a list. So let's look at the Design View of the Customer table and then add the SalesRep field. Instead of an AutoNumber, this foreign key must be designated with a number data type and a Long Integer Field Size. According to Access, that's the only field type that will work in this relationship. Setting the common field to 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.

Working with AutoNumbers as foreign keys
Video duration: 2m 24s 3h 43m Intermediate

Viewers:

Working with AutoNumbers as foreign keys provides you with in-depth training on Business. Taught by Alicia Katz Pollock as part of the Access 2010 Power Shortcuts

Subject:
Business
Software:
Access
Author:
please wait ...