Including Multiple Tables in Access Databases

The following activities are designed to introduce you to the use of multiple tables in Access databases. The exercises are designed for you to perform as you read, so access to the Microsoft Access program and the database Customer developed in Lessons 2-5 is highly desirable.

1. Defining a related second table for the Customer database.

Suppose we wish to add a table containing information about purchases our customers have made. In particular, for each item purchased, suppose we wish to track the date of the purchase, stock number for the item bought, unit price, total number of units bought, and method of payment (cash, card, check). You will create a seccond table named Purchases for this purpose.

Open the Customer database. Select the Tables tab in the main database window and then double click on the Create Table in Design View from the menu selection as illustrated below.

Using the design view window, enter the seven field names and data types for the information we wish to store as shown in the figure below. Once you've entered and checked this information, close the window and save the table under the name Purchases. When asked if you want to define a primary key, answer Yes and Access will make the orderno field the primary key field.

2. Relating the two tables.

We now need to define how the two tables -- CustomerList and Purchases -- will be related. Of course we intend for the relationship to be contained in the custno data. Note that each record in the Purchase table will be related to a single record in the CustomerList table. On the other hand, a record in the CustomerList table may be related to many records in the Purchases table. In other words, a customer can make many purchases, but each purchase is made by only one customer. We say that the relationship from CustomerList to Purchases is one-to-many.

Let's see how we communicate all this to the database system. When you're returned to the main database window, select the Relationships command from the Tools menu as shown below. In the Show Table dialog box that results add both the tables to the Relationships window by selecting them in turn and clicking Add. Once you've done this click Close to close the Show Table window.

Now move the mouse pointer over the custno field name in the CustomerList table (be sure you do this for the CustomerList table). Click and hold the mouse button down and drag the pointer (it will change shape) over the custno field name in the Purchases table and release. You will then see a dialog box like the one below. Make sure the custno field name from the CustomerList table is on the left under the Table/Query heading and the custno field name from the Purchases table is on the right under the Related Table/Query heading. If these are reversed, you dragged the mouse in the wrong direction. You should press Cancel and repeat this step from the begininng, being careful to follow the directions carefully.

Check the Enforce Referential Integrity box as shown in the figure. This will ensure that you cannot enter a purchase record in the Purchases table unless there is already a corresponding customer record (i.e., corresponding custno values) in the CustomerList table. Hence we will be protected from making certain kinds of typographical errors as we enter data. If a new customer makes a purchase, we must always place a record for that customer in the CustomerList table BEFORE we place the purchase record in the Purchase table.

Once you click Create in the previous dialog box, you will see a figure like the following (the tables may be placed in different positions in the screen -- these can be dragged and moved). Notice the connecting link between the two tables. The two symbols 1 and "infinity" (the 8 on its side) are there to emphasis that this is a one-to-many relationship from table CustomerList to table Purchases. The non-symmetrical nature of this relationship is why we needed to be careful about the direction we dragged the mouse pointer when we linked these two tables earlier in the process.

3. Creating an entry form for the new table and adding data.

Close the Relationships window and you'll be returned to the main database window. Select the Forms tab, then double click on the Create Form By Using Wizard from the menu selection. Next choose the Purchases table as shown below.

Go through the forms wizard to create a columnar form containing all the Purchase table's fields (you choose the style). Name the form EnterPurchases. Now use the form to enter the following records into the table (shown in datasheet view here).