When you design an Access table, you are expected to designate a primary key. If you do not, then when you save the table, you are asked if you want to.

Why would you want to designate a primary key?

(article continues after sponsor spot)

Here is a quote from the Access 2000 Help file.

The power of a relational database system such as Microsoft Access comes from its ability to quickly find and bring together information stored in separate tables using queries, forms, and reports. In order to do this, each table should include a field or set of fields that uniquely identifies each record stored in the table. This information is called the primary key of the able

Access Help goes on to recommend 3 types of Primary Keys.

1. autonumber
2. single-field
3. multiple-field

The key to primary keys is that they have to be unique. Every record in the table has to have a unique primary key. An example of a single-field primary key in a table of products could be the UPC code.

If there are no unique fields, then some would suggest a multiple-field key. An example of this would be in an Order Details table. The Order ID is not unique as a single order can have multiple products. The Product ID is not unique as a product could be in more than one order. However, the combination of Order ID and Product ID is unque.

My personal favourite is the Autonumber key available in MS Access. It is automatically unique, and is not directly needed by the users. They never have to see it. It also fits nicely with my unbound forms methods.

In other articles on the Access Database Tips site, you will regularly see my use of autonumber fields. Enjoy the articles, and consider subscribing to my free ezine. Click on the subscribe button to the left, fill in the form, and click the Submit button.

If you want to learn more about primary keys and the theory of designing proper table structures, visit Database Design Resource (note: a new window will open). Alf has an excellent understanding of those topics, as well as topics like database normalization. Topics that are essential, especially when designing larger databases.

Happy Coding

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

© 2006, 2007 Richard W. Killey. All Rights Reserved.