outer join pull out duplicate records

[Nadalia]

I was trying to use outer join to join 4 tables together, so that I can run a keyword search across these tables. The tables that I use to join are: tblMailing (main table), tblSPerson, tblAddress, tblContact. The relationship between tblMailing and tblAddress is one to many, and between tblMailing and tblContact is one to many as well.

Here is my big outer join query:

SELECT tblMailing.mailingID, tblMailing.MLabel, tblMailing.Membership, tblMailing.Company,
tblSPerson.SFirstName, tblSPerson.SLastName, tblSPerson.SCompany,
tblAddress.Address1, tblAddress.Address2, tblAddress.Address3, tblAddress.City,
tblAddress.State, tblAddress.ZipCode, tblAddress.Country,
tblContact.Phone, tblContact.Email

FROM (((tblMailing LEFT OUTER JOIN tblSPerson ON tblMailing.mailingID=tblSPerson.mailingID)
LEFT OUTER JOIN tblAddress ON tblMailing.mailingID=tblAddress.mailingID)
LEFT OUTER JOIN tblContact ON tblMailing.mailingID=tblContact.mailingID)

---------
The problem is that the search result pulls out duplicate records when a record has two different phones or addresses(office, home). If there are more that one phone or address, how can I let my query add secondary phone or secondary address to the end of the record instead of pulling out the records twice with all info.same but phone and address?

Thanks in advance!

Nadalia

 

[Pat Hartman]

You can't do that with a query. You can only do it with VBA. If you present your data as a report rather than a query, the problem won't exist if you use subreports for the many-side data.