The Tables for a Multi-Product


Some Background.  We talked about in a previous week.  At that time, Madam Curry was only offering her customers a single book or the possibility to join her email list.  But we will expand on this somewhat now that she has added two smaller cookbooks to her more encyclopedic effort.  So the titles of her books are
  • Hearty Vegetarian Cooking
  • Quick Recipes for the Vegetarian in a Hurry
  • Vegan Recipes

The entities we will focus on in this example are

  • customers for the email list
  • purchasers
  • books
  • orders

Notice that I break an HPM rule by using plural nouns.

For the sake of saving you the effort of going back to previous webpages, we will delineate the fields we want for the e-mail list.

table: email_list

id_email_list autonumber
  fk_id_purchaser number
  first_name text
  middle_name text
  last_name text
  street_address text
  city text
  state text
  zipcode text
  phone_home text
  phone_business text
  phone_cell text
  email_address text


The data for the purchasers will be largely similar, but we need to differentiate between shipping and billing information.  Notice, I have modified the table contents slightly to more accurately reflect the situation.


table: purchasers

id_purchaser autonumber
  bill_first_name text
  bill_middle_name text
  bill_last_name text
  bill_street_address text
  bill_city text
  bill_state text
  bill_zipcode text
  bill_phone_home text
  bill_phone_business text
  bill_phone_cell text
  bill_email_address text
  include_in_email_list yes/no
  bill_same_ship yes/no
  ship_first_name text
  ship_middle_name text
  ship_last_name text
  ship_street_address text
  ship_city text
  ship_state text
  ship_zipcode text
  ship_phone_home text
  ship_phone_business text
  ship_phone_cell text
  ship_email_address text


Now we need a data dictionary for the table for the products or books.


table: books

id_book autonumber
  title text
  publisher text
  copyright text
  price currency


I have set the data type for the copyright field as text.  This is because the date/time data type has quite a few restrictions in Access.  Copyrights are almost always entered as just the year of the copyright.

But since our purchasers may by more than one book, we really should be keeping track of their orders.


table: orders

id_order autonumber
  fk_id_purchaser number
  fk_id_book number
  date_of_purchase date/time
  amount currency



Now I want to specify the relationships between these tables.  I am going to try and lead you through developing these relationships in Access.

Make sure you are located on the tables within your madam_curry.mdb

Select the Tools menu.

In the tools menu select the Relationships item.

You should have a small blank screen open unless you have already developed some other relationships..

Right click in this blank form to bring up a popup menu.

Select the Show Table item in this popup menu.

Then select each of the tables, by clicking on their name and then clicking on the Add button.

This will make the tables available for use in the form.

Now you want to start connecting keys in one table to foreign keys in another table until you get something like the following image.



Notice I have the tables from left to right in the order

email_list   -   purchasers   -   orders   -   books

You start by clicking on the key in one table and then while holding down the button you drag to the foreign key you want in the next table.  This way you create the relationships by selecting the create button.

  • The id_purchasers in purchasers to fk_id_purchasers in email_list should be a one-to-one relationship.
  • The id_purchasers in purchasers to fk_id_purchasers in orders should be a one-to-many relationship.
  • The id_book in books to fk_id_book in orders should be a one-to-many relationship.

Constructing these relationships is important for developing forms, subforms, queries and reports that reflect the inter-relationships among the tables and data.

You should click on the following link to download my copy of madam_curry.mdb to save to your computer.  Then you can experiment with it as you'd like.


download madam_curry.mdb