Conditional Query to Concatenate Fields

[gswan]

I have a table of data about people. The fields I'm working with are PersonID, FirstName, LastName, and SpouseID. PersonID is the primary key. If two people (in different rows) are married, they each have their spouses PersonID number in their SpouseID field. Single people don't have any entry in their SpouseID field. What I would like to do get a list of everyone from the table, with married couples listed together on one row. A sample of the data would be:

PersonID / FirstName / LastName / SpouseID
1 / John / Smith / 3
2 / Mark / Jones / 5
3 / Mary / Smith / 1
4 / Paula / Doe / null
5 / Jane / Jones / 2
6 / Harry / Johnson / null


What I would like the query to produce would be:

FirstName / LastName
John & Mary / Smith
Mark & Jane / Jones
Paula / Doe
Harry / Johnson

So far, this is what I've got - I'm still a long way off!

SELECT [tblMembers.FirstName] & " & " & [tblMembers_1.FirstName] AS CouplesNames, tblMembers.LastName
FROM tblMembers LEFT JOIN tblMembers AS tblMembers_1 ON tblMembers.PersonID = tblMembers_1.SpouseID;

 

[MStef]

I think I guess what you want.
Look at "DemoCondqueryA2000.mdb" (attachment, zip).
There are 4 queries, 2 tables, Module1 and Form1.
Open form1 and try.
Maybe you need to adapt something to your data.

 

[gswan]

Yes, that .mdb example does what I'm looking for! Thanks! Now I just have to learn a lot more about visual basic and what you did with those queries!