I want to be able to do a mail merge but only print out one label per address in the Contact table, but sometimes that one address may belong to more than one person, so if that is the case then I want to concatenate those two names on the one label. e.g.
70 Smith Lane
Harry & Mary Booth
45 Dean Ave
Where Harry and Mary Booth are two separate records in the Person table with the same ContactID
GROUP_CONCAT is not just concatenation -- it is the concatetnation of values from a column
anyhow, your approach will work, but you'd need a left outer join (to handle conditions where there's only one person per contact), and then you'd probaby want to go to three copies of the person table in case there are three people at the same address, or maybe four ...
... and it would get very inefficient very quickly
Sorry I posted that before I realised that you had just given me a hand, my mistake.
That looks very impressive, thankyou, just one small problem, and I think its a MS Access problem. It gives me a 'Join expression not supported error'
I think the problem is with the:
AND P1.MemberID <> P2.MemberID
part, as if i remove this it works, but not with the required result obviously, any ideas how to make this Access friendly?
SELECT P1.FirstName + IIF(ISNULL(P2.FirstName), '', ' & ' + P2.FirstName), C.FlatHouseNo, C.StreetName, C.TownCity FROM (Contact C INNER JOIN Person P1 ON (C.ContactID = P1.ContactID)) LEFT OUTER JOIN Person P2 ON (C.ContactID = P2.ContactID AND P1.MemberID <> P2.MemberID)
yeah i am sure, i have put parentheses everywhere, but same error, but I dont think that alone is the problem because i took out the first join changed a few thing to make it work, then the second join did work as it is,
so its a combination of the two joins that is the problem...