MS-Access / Getting Started

---

Defining Relationships

After you have defined two or more related tables, you should tell Access 2010 how the tables are related. You do this so that Access 2010 will be able to link all your tables when you need to use them in queries, forms, or reports.

Thus far in this tutorial, you have seen how to build the main subject tables of the Contact Tracking database-Companies, Contacts, and Products. Before we define the relationships in this sample database, you need to create a couple of linking tables that define the many-to-many relationships between the Companies and Contacts tables and between the Products and Contacts tables. Table-9 shows you the fields you need for the Company Contacts table that forms the "glue" between the Companies and Contacts tables.

Table-9 Field Definitions for the Company Contacts Table

Field Name	Data Type	Description	Field Size
CompanyID	Number	Company/organization	Long Integer
ContactID	Number	Person within company	Long Integer
Position	Text	Person's position within the company	50
DefaultForContact	Yes/No	Is this the default company for this contact?

Define the combination of CompanyID and ContactID as the primary key for this table by clicking the selection button next to CompanyID and then holding down the Ctrl key and clicking the button next to ContactID. Click the Primary Key button in the Tools group of the Design tab on the ribbon to define the key and then save the table as CompanyContacts.

Table-10 shows you the fields you need to define the Contact Products table that creates the link between the Contacts and Products tables.

Table-10 Field Definitions for the Contact Products Table

Field Name	Data Type	Description	Field Size
CompanyID	Number	Company/organization	Long Integer
ContactID	Number	Related product	Long Integer
ProductID	Number	Related product	Long Integer
DateSold	Date/Time	Date product sold
SoldPrice	Currency	Price Paid

The primary key of the Contact Products table is the combination of CompanyID, ContactID, and ProductID. You can click CompanyID to select it and then hold down the Shift key while you click ProductID (if you defined the fields in sequence) to select all three fields. Click the Primary Key button in the Tools group of the Design tab on the ribbon to define the key, and then save the table as ContactProducts.

You need one last table, the Contact Events Table, to define all the major tables you'll need for Contact Tracking. Table-11 shows the fields you need. The primary key for this table is the combination of ContactID and ContactDateTime. Note that we took advantage of the fact that a Date/Time data type in Access 2010 can store both a date and a time, so we don't need the two separate date and time fields. Save this last table as ContactEvents.

Table-11 Field Definitions for the Contact Events Table

Field Name	Data Type	Description	Field Size
ContactID	Number	Related contact	Long Integer
ContactDateTime	Date/Time	Date and time of the contact
ContactNotes	Memo	Description of the contact
ContactFollowUpDate	Date/Time	Follow-up date

Now, you're ready to start defining relationships. To define relationships, first close any Table windows that are open and then click the Relationships command in the Relationships group of the Database Tools tab on the ribbon to open the Relationships window. If this is the first time you have defined relationships in this database, Access 2010 opens a blank Relationships window and opens the Show Table dialog box.

In the Show Table dialog box, select each table and click Add in turn. Click Close to dismiss the Show Table dialog box.