|| Feedback | Things you can do with a query ||
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 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 saved 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 the 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).
|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|
|HIGH FLYERS ATHLETICS||Miss J Valin||The Track, Wembleyfirstname.lastname@example.org||CLUBS||No|
|CAPED CRUSADERS CLUB||Mr B Wayne||The Cave, Highwayemail@example.com||YOUTH||Yes|
|ST MARTINS CHURCH CHOIR||Mr W Mozart||The Abbey, Vienna||N||OTHER||No|
|CHELSFIELD GIRL GUIDES||Mrs A Boleyn||The Castle, Heverfirstname.lastname@example.org||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)
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
|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 June 06||© MeadInKent.co.uk 2006|