I have a mastermember table in which each record represents a family. The key field is [membershipID]. I have a members table which represents individual people. The key field is [memberID]. Any number of people may represent a family. I need to develop a table which contains a single row for each family and each member of the family needs to be included.
I need this because I've been ask to produce a spreadsheet containing all family members which can be used in a mail merge.
Creating a query to select the [head_of_house] is simple but how do you select the other family members when there may be none, one or any number?
Using the members table I have the following fields.
membershipID ; used to group the family
memberID ; to identify each member of the family
membertype ; head, spouse, son, daughter
I know there needs to be a loop involved by I don't know how to execute it.
Loop through all [membertype = son] where [membershipID = head.membershipID] ?
Any suggestion on a concept, a direction to start?
Attached is a sample database and spreadsheet.