A Tale of Two Tables (and a Master) – Getting a report containing data from two details tables

By C L Kim | Posted on September 11th, 2008

A few weeks ago, I was asked the following question, edited here for clarity:

 “I have a master table called Contacts. It’s related to a details table called Addresses. The Addresses table has a column called ‘current’. Only one address per contact should be marked current. There’s a third table – Donations. Contacts is a master table for Donations as well.

 What we’d like is a report of all donations, listing contact name, city and state. I’m wondering if I should create a relationship between Donations and Addresses…that does not seem right.”

So, graphically, what we have are the relationships
 Contacts ∈ Addresses
 Contacts ∈ Donations

You probably know we need to create a report in the Donations table; but how to access the address information in another (sort of related) details table?

Ultimately, in a Step 2, we need to have the one current address record’s information (from out of all the related records in the Addresses details table) to be represented in the Contacts master record. That will then enable us to create Lookup fields in the Donations details table, and hence to create the report in the Donations table.

Step 1. The way to represent text data in the Contacts table from the Addresses table is to create a new relationship in the ‘opposite direction’, in which the Addresses table is a master table to the Contacts table, which then acts as a details table. But, how would the contact record find the one correct address record to become its master?

 1.1  From the original
  Contacts (master) ∈ Addresses (details)
relationship, create a Summary field in the Contacts master table. Select, say, the “Maximum” summary function (in this particular example, other summary functions such as “Total” or “Minimum” would work as well) for the [Record ID#] field in the Addresses table, but only for address records whose [current] field is checked. Do this by setting that condition as the “Matching Criteria” for the Summary field.

 1.2  Create a new relationship in which
  Addresses (master) ∈ Contacts (details)
For this relationship, make the above Summary field in the Contacts table the Reference field to the Addresses table’s [Record ID#] key field.

 1.3  Finally, in the new relationship of step 1.2, create Lookup fields in the Contacts table for data in the Addresses table that you’d like to be represented in the Contacts table.

Now we can proceed with Step 2.