MeadInKent
| Feedback | Things you can do with a query |

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).

2 Bookings: Table ox
Bkg Ref User Group Out Back Km Start Km End Inv Raised
2436 CAPED CRUSADERS CLUB 16 Jul 05 16 Jul 05 69532 70121 21 Jul 05
2437 CHELSFIELD GIRL GUIDES 19 Jul 05 19 Jul 05 70121 70246 21 Jul 05
2438 ST MARTINS CHURCH CHOIR 28 Jul 05 28 Jul 05 70708 70988 02 Aug 05
2439 HIGH FLYERS ATHLETICS 23 Jul 05 23 Jul 05 70246 70335 02 Aug 05
2440 ST MARTINS CHURCH CHOIR 27 Jul 05 27 Jul 05 70335 70706 02 Aug 05
2449 CHELSFIELD GIRL GUIDES 10 Oct 05 10 Oct 05 75788 75848 23 Oct 05
2442 ST MARTINS CHURCH CHOIR 19 Aug 05 31 Aug 05 72349 73792 08 Sep 05
2443 HIGH FLYERS ATHLETICS 18 Aug 05 18 Aug 05 72248 72349 08 Sep 05
 

UserGroups: Table ox
Group_Name Leader Address_One Email_Addr Group_Type Charity_YN
HIGH FLYERS ATHLETICS Miss J Valin The Track, Wembley jvalin@nhs.com CLUBS No
CAPED CRUSADERS CLUB Mr B Wayne The Cave, Highway brw@hotmail.com YOUTH Yes
ST MARTINS CHURCH CHOIR Mr W Mozart The Abbey, Vienna N OTHER No
CHELSFIELD GIRL GUIDES Mrs A Boleyn The Castle, Hever aboleyn@heverguides.co.uk YOUTH No
 

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:            
 

 
UK more...   USA 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.

Bookings_with_GroupDetails: Select query ox
Bkg Ref Out Back User Group Leader Address_One
2436 16 Jul 05 16 Jul 05 CAPED CRUSADERS CLUB Mr B Wayne The Cave, Highway
2437 19 Jul 05 19 Jul 05 CHELSFIELD GIRL GUIDES Mrs A Boleyn The Castle, Hever
2438 28 Jul 05 28 Jul 05 ST MARTINS CHURCH CHOIR Mr W Mozart The Abbey, Vienna
2439 23 Jul 05 23 Jul 05 HIGH FLYERS ATHLETICS Miss J Valin The Track, Wembley
2440 27 Jul 05 27 Jul 05 ST MARTINS CHURCH CHOIR Mr W Mozart The Abbey, Vienna
 


file: accqueryjoins.htm Page last updated Mar14 MeadInKent.co.uk 2014