Hi..I am using Access 2007 and am creating a membership directory.

I have created the following tables:

tblFamily: includes address, phone, e-mail - primary key is fldFamID

tblDependents: includes family member's first name, last name, DOB, relationship (husband, wife, child) - primary key is a combination of fldFamID and fldMemID

tblFamily and tblDependents have a One-to-Many relationship

I am now trying to create the directory and a series of mailing labels that I would addressed to the heads of the family (relationship codes 1 and 2)

ie: Mr John and Mary Smith (data stored in tblDependents)
123 Main Street (data stored in tblFamily)
Anywhere, St 12312 (data stored in tblFamily)

My problem is that John Smith is fldFamID: 4, fldMemID:1 and Mary Smith is fldFamID: 4, fldMemID: 2

John is fldRelID: 1 (male spouse)
Mary is fldRelID: 2 (female spouse)

How can I get their names into the same textbox on the report?


Thanks!

Make a text box with both his and her names with an iif to add the the word AND between them if there is both.
change the firel names cause i call it fldHisName for male fldHerName for female and fldLastName for surname.

=[fldHisName] & IIf([fldHisName] & [fldHerName] <>" ", " and ", "") & [fldHerName] & " " & [fldLastName]

Make a text box with both his and her names with an iif to add the the word AND between them if there is both.
change the firel names cause i call it fldHisName for male fldHerName for female and fldLastName for surname.

=[fldHisName] & IIf([fldHisName] & [fldHerName] <>" ", " and ", "") & [fldHerName] & " " & [fldLastName]

This could work, except that "he" and "she" are separate records in table...I created a separate record for each family member because I have other information that is individual specific, however mailings/directory listings should be for the family.

Is there is way to get "he" and "she" from separate records and combine them into a single textbox?