Access queries - linking tables

It is simple to create tables of data in Excel and interrogate them using filters and pivot tables. The power and advantages of using Access for analysing data becomes apparent when you wish to link various tables together.

There is a golden rule in database design that whenever possible, data should only be stored once. Therefore if you have a table of bookings, you should not re-write the complete address and contact details of the customers in every booking record. This is likely to lead to much repetition and will be very time consuming to update if a customers details should change. Instead, it is much better to have one table for the bookings and a separate table for the customer names and addresses. When designing your database you must ensure that the tables have common fields (such as the Customer name).

So long as there is a UserGroup record containing a Group_Name which matches each of the User_Group fields in the Bookings table, it is possible to instruct Access to link the fields and thereby join both tables together.

A new query can be created and both tables added to it. Drag the cursor between the two related fields in order to create a link.

Bookings_with_GroupDetails: Select query (Design)						ox
Field:	Bkg_Ref	Date_Out	Date_Return	UserGroup_Name	Leader	Address_One
Table:	Bookings	Bookings	Bookings	Bookings	User_Groups	User_Groups
Sort:	Ascending
Show:	Y	Y	Y	Y	Y	Y
Criteria:
Or:

	more...			more ...

In most circumstances one of the tables will have the linked field configured as being a Unique Key (or index) and duplicates are not allowed. The query can therefore be described as 'One to Many'. In this example there can only be a single record for each UserGroup name. If the group leader changed address and a second record was added with the same Group_Name, the link between the tables would become problematic and result in duplicate Bookings records being displayed in the query. Therefore either the original UserGroups record must be edited and the address changed, or a completely new record with a different Group_Name must be added.