Queries which Join Records

Next: Auto-Generated Fields, Previous: Grouping and Aggregates, Up: Top

11 Queries which Join Records

Suppose you wanted to add the residential address of the people in the acquaintances.rec file from Simple Selections.

One way to do this is as follows:

     %type: Dob date
     
     Name: Alfred Nebel
     Dob: 20 April 2010
     Email: alf@example.com
     Address: 42 Abbeter Way, Inprooving, WORCS
     Telephone: 01234 5676789
     
     Name: Mandy Nebel
     Dob: 21 February 1972
     Email: mandy@example.com
     Address: 42 Abbeter Way, Inprooving, WORCS
     Telephone: 01234 5676789
     
     Name: Bertram Nebel
     Dob: 3 January 1966
     Email: bert@example.com
     Address: 42 Abbeter Way, Inprooving, WORCS
     Telephone: 01234 5676789
     
     Name: Charles Spencer
     Dob: 4 July 1997
     Email: charlie@example.com
     Address: 2 Serpe Rise, Little Worning, SURREY
     Telephone: 09876 5432109
     
      Name: Dirk Spencer
     Dob: 29 June 1945
     Email: dirk@example.com
     Address: 2 Serpe Rise, Little Worning, SURREY
     Telephone: 09876 5432109
     
     Name: Ernest Wright
     Dob: 26 April 1978
     Email: ernie@example.com
     Address: 1 Wanter Rise, Greater Inncombe, BUCKS

This will work fine. However you will notice that there are two addresses where more than one person live (presumably they are members of the same family). This has a number of disadvantages:

You have to type (or copy) the same information several times.
Should a family move house, then you would have to update the addresses (and telephone number) of all the family members.
A typing error in one of the addresses would lead an automatic query to erroneously suggest that the people lived at different addresses.
It unnecessarily increases the size of the recfile.