Part I: Access Building Blocks
116
However, there is no reason why the primary key value has to be meaningful to the application.
A primary key exists in a table solely to ensure uniqueness for each row and to provide an anchor
for table relationships. Many Access developers routinely use AutoNumber fields as primary keys
simply because they meet all the requirements of a primary key without contributing to an applica-
tion’s complexity.
Table 3.1 lists the Collectible Mini Cars tables and describes one possible plan for deriving the
primary key values in each table. As this table shows, it doesn’t take a great deal of work (or even
much imagination) to derive a plan for key values. Any rudimentary scheme with a good sequence
number always works. Access automatically tells you when you try to enter a duplicate key value.
To avoid duplication, you can simply add the value of 1 to the sequence number.
TABLE 3.1
Deriving the Primary Key
Table Possible Derivation of Primary Key Value
tblCustomers Companies: AutoNumber field assigned by Access
tblSales Invoice Number: AutoNumber field
tblSalesLineItems Invoice Number (from Sales) and an AutoNumber field
tblProducts Product Number, entered by the person putting in a new product
tblSalesPayments Invoice Number (from Sales) and an AutoNumber field
tblSalesperson Sales Person ID: AutoNumber field
tblCategories Category of Items: Entered by the person putting in a new record
Even though it is not difficult to use logic (implemented, perhaps, though VBA code) to generate
unique values for a primary key field, by far the simplest and easiest approach is to use
AutoNumber fields for the primary keys in your tables. The special characteristics of the
AutoNumber field (automatic generation, uniqueness, the fact that it cannot be changed, and so
on) make it the ideal candidate for primary keys. Furthermore, an AutoNumber value is nothing
more than a 4-byte integer value, making it very fast and easy for the database engine to manage.
For all these reasons, the Collectible Mini Cars exclusively uses AutoNumber fields as primary
keys in its tables.
You may be thinking that all these sequence numbers make it hard to look up information in your
tables. Just remember that, in most case, you never look up information by an ID field. Generally,
you look up information according to the purpose of the table. In tblCustomers, for example,
you would look up information by customer name — last name, first name, or both. Even when
the same name appears in multiple records, you can look at other fields in the table (zip code,
phone number) to find the correct customer. Unless you just happen to know the customer ID
number, you’ll probably never use it in a search for information.
