Once you have ERDs prepared, you can prototype tables and test data in Excel. On a single sheet, you can place the following tables.
Note that we have assigned a customer number (C#) to be a primary key for the Customer table. For the test data in this example, we have entered consecutive numbers for the C#.
We have assigned a product number (P#) to be the primary key for the product table. We also entered consecutive numbers for P#.
We assigned an order number (O#) to be the primary key of this table. Note that no customer or product names appear. Instead, the customer number and product number appear as foreign keys. This is hard for people to read perhaps, but the machine has no difficulty with it.
This is one reason why I suggest that you set up test data in an Excel spreadsheet first (for the first few times you try this, at least) rather than trying to enter everything directly into Access tables. The Excel tables are a little easier to see/understand, and more people seem to know how to put entries into an Excel spreadsheet than into Access tables, at least initially. After a few tries at building linking tables, you may not need to use the Excel stopgap any more. In fact, you may find it easier or quicker to use automatic counter variables in Access as your primary keys. We can talk about that later in the term.
The telephone table handles the repeating group (Customer 1:M telephones). Note how this table is arranged. By using a number field and a description field, any kind of phone can be tracked. If now we have home, office, and fax phones, tomorrow we could add mobile phones to the description list (e.g., mobile) and we would not have to change the design of the table. All that would change is that "mobile" would become a new value in the Description field.
Note also how the table handles the one-to-many relationship. For example, two phones are listed for customer #1 - a home phone and an office phone. Three phones are listed for customer #2. You would reassemble these phones in a query or a form using the T# key and the C# foreign key to build links between the Customer table and the Telephone table.
If you have used Excel to place test data in your tables, the next task is to get the data into Access. Accomplish this by copying from each table into Access. This example is for Access 7.0.
tab in the main
database window. Selecting 
will open the New Table dialog box. In that dialog box, tell Access
you would like to open the table in Datasheet View:
).
After another "Do you really want to do this?"
dialog box, Access will ask you to name the table. Here I've used
the name Sample Table. You would use Customer or Product or Order
or Telephone, etc.
, select
the C# field, and click on the Primary Key toolbar button 
.
You will see a primary key symbol appear next to the C# field
in your table. This means you have set that field as the primary
key.
This section will assume that you have built Customer, Product, Order, and Telephone tables in Access. This step involves identifying to Access the relationships that you specified between entities in your ERD.
Why define relationships? It is certainly true that you can define relationships between tables whenever you build a query or a form. In a meaningfully large database, however (e.g., more than 10 tables, more than 15 queries, more than 20 forms), it can be very helpful to specify relationships early because it can save you time as you design the remaining parts of the database. Any relationship that has been set between tables using the Tools Relationships command, for example, will automatically become active in a query or form that involves the tables on either side of the relationship. As you start to work with more complicated data structures, using the Relationships window to translate ERDs directly into table designs can be helpful.
To define relationships, open the Relationships Window using Tools Relationships (In Access 2.0, this is the Edit Relationships command). This menu choice is not always available - you may want to click on the Tables tab of the database window to make it active. If no tables are selected for relationships in your database, opening the window triggers an Show Table dialog box. Click, shift-click, or ctrl-click the tables you want to add.
Note that you can construct links between tables, queries, or both. In this example we'll only worry about tables.
Suppose, for example, that we want to create a 1:M (one-to-many) relationship between the customer and orders tables (e.g., each customer may place one or more orders). To do this, add the Customer and Orders tables to the Relationships window:
Then use your cursor to drag the CID field from the Customer table (e.g., where it is the primary key) and drop it over the CID field in the Orders table (where it is the foreign key - the "many" side of the one-to-many relationship). When this works you will get the following dialog box:
Note in the Relationship Type section that Access has figured out what kind of relationship you are trying to establish because it sees you have dragged a primary key (e.g., a unique identifier index) onto a foreign key (e.g., in another table). Notice also that you can enforce so-called referential integrity, meaning that if you delete a customer all the customer's related orders will also be deleted ("cascade" delete).
If you select Enforce Referential Integrity and then click the Create button in this dialog box, you will see a picture in the Relationships Window that looks very much like your ERD, with the added detail that relationships between key fields are now explicit:
Recall that in the Orders exercise we are using the simplifying assumption that each order represents a request for only one product, but that any customer may order many products and any product may be ordered by many customers. In this sense, Orders becomes a linking table (what Access 7.0 refers to as a junction table). That means you need to "break" the M:M relationship using two 1:M relationships. The final relationships window for Orders thus looks something like this:
The Orders exercise asks that we build a query to show orders by customer, sorted by date.
Looking at the tables and relationships above suggests that you will at least need the following data (e.g., fields) to answer this question:
This suggests a query that links all three tables. To build this
query, go to 
(the Queries
tab in the database window) and choose .
You will get the New Query dialog box. Select Design View.
In the resulting Show Table dialog box, use Ctrl-click to select
the Customer, Products, and Orders tables. Please note that your
table list may not look exactly like the one below.
When you choose Add and then Close from the Show Table dialog
box, you will see the tables you selected in the Query Design
View. Note that the relationships you specified earlier are already
shown and active in this window.
To select the fields you need, double-click on each field in turn
in the tables at the top of the query design window. For example,
here is the same screen after I double-clicked Name in the Customer
table and Name in the Product table:
Note that even though the fields have the same label (e.g., "Name"),
Access is not confused because it recognizes that the Name fields
come from different tables (note the entries placed in the Table
row in the bottom half of the query design window). When your
query runs, the fields will be identified as Customer.Name and
Products.Name , as shown below:
To complete the Orders Exercise, design and save a query that includes the tables and fields you need to show who ordered what when. To transfer your results to a Word file, use the techniques we discussed for the Returns exercise.