Create relationships between tables

We have created two tables but as of now they are separate entities without any relationships. We now need to create a link between the two tables so that we are able to identify animals belonging to a farmer. Obviously, each farmer can have a number of animals; therefore, we will create what is called a One to Many relationship – meaning that an observation in the FARMER table will be linked to many observations in the ANIMAL table.

Like other Microsoft programs, there are many different ways to create links in Access.

This is one of the simpler approaches:

  • Open the ANIMAL table in Design view.
  • Go to the FARMERID field that we want to link with the FARMER table. Select Look up wizard from the Data Type column to start the wizard.
  • Leave the default option as it is, i.e. ‘I want the lookup column to lookup the values in a table or query’ and click Next.
  • Select the table with the parent field, i.e. the FARMER table. Note: the FARMER table is automatically selected as you have only one table other than the ANIMAL table.
  • Specify fields that you want to show in the combo-box (say FARMER ID, FirstName and the LastName) and click Next.
  • Click Next and Finish the wizard to create the relationship.
  • To check that the link has in fact been created, go to Tools>Relationships and verify that the two tables linked at FARMERID.

Although I do not want to go into too much detail in this introductory tutorial, you can double click the link (line) between the tables and edit relationships. Particularly, you can select the Enforce Referential Integrity and Cascade Update Related Fields check boxes. Referential integrity means that you cannot enter animal data without first entering the corresponding farmer record, i.e. you cannot have ‘orphan’ records.

Cascade Update Related Fields property means that if you change the FARMERID for a farmer in the FARMER table, it is automatically updated in the related the ANIMAL table.

For further information about these properties – search for the document ‘About relationships in an Access database (MDB)’ in MS Access help.