Page 7 of 11PREVNEXT

Design tables for a new Access 2007 database

Every database table needs a primary key

A critical field for all tables

A final step in your plan is to add a primary key field to each of your tables. A primary key is a field, or a combination of fields, with a value that makes each row in a table unique. Primary keys are another way to avoid duplicating your data, because you can never duplicate a value in a primary key field.

Primary keys also enable relationships among your tables. That's what makes your data powerful, because relationships allow you to ask questions and extract meaning from your data. We'll look at them in the next screen.

For now, remember that Access provides several ways to create primary keys. You can use existing values such as part numbers, but only if each value will always be unique. Once you define a field as a primary key, Access never lets you enter duplicate values in that field. Also, your key values should never change, because if they do your tables may become out of synch. If your existing data won't work as a primary key, you can use an AutoNumber field. Access simply increments the value in that field by one whenever you add a new record.

So how do you plan primary keys? Start by adding an "ID" field, such as "Asset_ID", to each of your tables. Or, if you want to use multiple columns of data to create a composite key, make a note next to each column you want to use. The next course in this series shows you how to create a primary key.

Page 7 of 11PREVNEXT