How do I merge two records into one for label purpose?

[Cosette]

Hi all, I have a table with info about people.

first name, last name, address, city state zip. When the address is a match, that means that they are married and I have a yes/no field showing that. My problem is that I want to print labels with both names and the same address. How do I get the query to merge the two name if the address is the same?

Thank you for your help.

David

 

[JerryKlmns]

David,

I would create a field MarriedTo containing the wife's primary ID for the husband record for such a relation.

Then create a query to retrieve only those relations and combine the fields like

SELECT TRIM(A.LName & " " & A.FName & " " & B.LName & " " & B.FName) As TheName, address, city, state, zip
FROM TablePeople As A INNER JOIN TablePeople As B
ON A.MarriedTo =B.PrimaryID
WHERE MarriedTo IS NOT NULL

UNION

SELECT (LName & " " & FName) As TheName, address, city, state, zip
FROM TablePeople
WHERE MarriedTo IS NULL;