The One to Many Relationship

In this post we are going to take a look at the One to Many Relationship in Access 2007.  We will go through the process of setting up a One to Many Relationship, but first, lets consider why they are necessary.

You may well be familiar with the term 'Relational Database' already, but perhaps don't understand what it means.  If that is the case, then, hopefully, this should put you on the right track to penetrating the meaning of the term.  Relational Database design is all about how database tables are connected with each other. Whilst the novice database designer will create one single table with lots of fields, the relational database designer will look at the the data that needs to be stored and will put it together in a number of groups.  Each group is then made into a separate table.  But because the subjects of the data stored in separate tables are connected in the real world - for example customers and orders - we need to create a relationship between the tables within the database itself.

Lets take a look at how we go about doing this in practice.  We will use a simplified example of a customer/order database.

Imagine we have a number of customers who order electronic products by email, and we want to create a database to record both the customer's details and their orders.  As we mentioned in the example above, we would go about this by separating the customer details and orders into two separate  groups which we then make into tables.  We do this for the simple reason that if a given customer makes more than one order, we only have to enter the customer details once in the customer table.  Because we are going to create a relationship between the customer table and the orders table, all orders for individual customers will be connected with the correct persons' customer details. This is made possible by connecting the primary key field of the customer table with the corresponding foreign key field in the orders table.  If you remember, Primary and Foreign Keys were covered in my last blog post.

1. The first step is to create a new Access Database. Lets call it Customer Orders.
   
   
2. Next create a new table called tblCustomers with the following fields - ID (autonumber, primary field), FIRSTNAME (text), SURNAME (text), ADDRESS1 (text), CITY (text), and POSTCODE (text).
   
   
3. Create a new table called tblOrders with the following fields - ORDERID (autonumber, primary field), ITEMORDERED (text), DATE (date), PRICE (number, set the format property to currency), CUSTOMERID (number).  CustomerId is going to be the foreign field when we create the relationship.
   
   
4. To create the relationship, select the DATABASE TOOLS RIBON  and click the RELATIONSHIPS icon (from the SHOW/HIDE group).
   
   
   
   
5. This opens the RELATIONSHIPS window.  You will also see the pop up SHOW TABLE form the first time you open the window.   We are now going to select which tables are going to be used in the relationship.
   
   
   
   
6. There are just two tables in our example database.  Click on tblCustomer to highlight it in blue if it is not highlighted already.  Then click add.  Do the same for tblOrder, then close the pop up SHOW TABLE form.You should now see the two tables represented as separate box's in the RELATIONSHIPS window.  You will notice in the diagram below that each box has it's fields listed and primary keys indicated.
   
   
   
   
7. We will now click the ID field in tblCustomer and drag it to the CUSTOMERID field in tblOrder.  Notice the mouse pointer image changes from a circle with a line across to a plus sign as it hovers over the fields in tblOrder.  When you release the mouse button at the end of the click and drag operation,  a new EDIT RELATIONSHIPS pop up form opens.
   
   
   
   
8. Click the three tick boxes which say ENFORCE REFERENTIAL INTEGRITY, CASCADE UPDATE RELATED FIELDS, and CASCADE DELETE RELATED RECORDS. We won't be going into what these do in this blog post.
   
   
9. Click the CREATE BUTTON.  Our table relationship is now in place.  This is what the database window looks like now.  Note the line drawn by Access between the ID field of tblCustomer and the CUSTOMERID field of tblOrder.
   

All we need to do now is enter some data and test out the relationship.  Try entering this data from the image below into tblCustomer:

Then enter this data from the image below into tblOrder:

Notice that each order in tblOrder has a CustomerId corresponding to one of the customer ID's in tblCustomer.  This is how we link each order to a particular customer.  To prove that this works, open up the tblCustomer table and click the + sign's (left of table) on each customers row.  This shows that Access has automatically linked each order from tblOrder to it's corresponding customer in tblCustomer.

All this gives us a basic idea of what table relationships are all about. There is, however, much more to it than this.  For example, once we have set up relationships like this, we may create forms, queries and reports that reflect these relationships, and exploit the advantages that this has over single table "flat file" database applications.  In the next blog post, I hope to write about creating a single Access Form which allows us to enter data into both Customer and Orders tables together.