Primary Key (PK)

A Primary Key (PK) is the name of the field or combination of fields that uniquely identifies each record.

It is common to define an AutoNumber field in each table to serve as the PK.

Foreign Key (FK)

A Foreign Key (FK) is the name of a field (or fields) that relates a record to another table. If the PK in the main table is an AutoNumber, then the Data Type of the FK in the related table should be Long Integer (Number).

Example Report

Assume you have the following information:

Table Structure

While you might be tempted to create just one table for this information, that would not be the most flexible way to store it. Each contact may have no phone or several. Each phone number has a type such as Home, Office, Fax, or Cell.

Set up the following tables:

Tracking Field

Each table has a tracking field called dtmAdd that is automatically filled out with the date and time the record was created. This is accomplished using Now() as the default value, which is a built-in function that returns the date and time of the system clock.

Example Records

Here are examples of records your tables may contain. Each table has an AutoNumber field that is designated as the Primary Key. AutoNumbers automatically get a value when a new record is created. Access ensures this value is distinct.

Contacts

Phone Types

The values of an AutoNumber don't make sense and they don't need to. The only thing that matters is that the values are unique for each record.

Phones

The Phones tables has 2 foreign keys:

1. CID identifies the contact that the phone number belongs to.
   For example: CID=1 —> MainName = Smith, NameFirst = Mary
   
   
2. PhoTypeID specifies the type of phone number
   For example: PhoTypeID=104 —> PhoType = Cell

Relationship Diagram

Layout your relationship diagram so that each table with a Primary Key is to the left of each table with a related Foreign Key. This way, the diagram will flow from left to right as data must be entered.

One Contact may have many Phone numbers. There is a one-to-many relationship between Contacts and Phones.

One Phone Type may be used in many Phone records. There is a one-to-many relationship between Phone Types and Phones.