Select Distinct problem? Need immediate help, please!

[modfather]

I'll get right to it. I have a query that pulls data from a few tables to do a simple display like this:

CardNum FName LName SchoolName CompNum compsort
101 Zakia Alameri Gavan 128 1
101 Zakia Alameri Gavan 129 2
101 Zakia Alameri Gavan 130 3
101 Zakia Alameri Gavan 231 4
101 Zakia Alameri Gavan 132 5
101 Zakia Alameri Gavan 133 6
101 Zakia Alameri Gavan 134 7
101 Zakia Alameri Gavan 902 8
102 Meghan Albamonte Gavan 5 1
102 Meghan Albamonte Gavan 6 2
102 Meghan Albamonte Gavan 7 3
102 Meghan Albamonte Gavan 8 4
103 Makulumy Alexander-Hill Claddagh 328 1
103 Makulumy Alexander-Hill Claddagh 329 2

My SQL Statement reads:
SELECt Dancer.CardNum, Dancer.FName, Dancer.LName, School.SchoolName, Results.CompNum, competition.compsort
FROM Competition INNER JOIN ((Dancer INNER JOIN School ON Dancer.SchoolID = School.SchoolID) INNER JOIN Results ON Dancer.CardNum = Results.CardNum) ON Competition.CompNum = Results.CompNum
ORDER BY dancer.cardnum, Competition.CompSort;

What I want to see is something like:

101 Zakia Alameri Gavan 128, 129, 130, 231, 132, 133, 134, 902
102 Meghan Albamonte Gavan 5, 6, 7, 8
103 Makulumy Alexander-Hill Claddagh 328, 329, 331

In other words, if I have a repeat of a row for my first field (which is in my Dancer table), then I want my Results.CompNum (which is my 5th field in my top example) to just repeat on the same line, comma separated.

I hope this makes sense. I'm kind of in a big hurry, so I'm hoping someone will come in and rescue me.

Thanks.
Steve

 

[scriverb]

Name your first query "qryResults" and then create three queries:
Create a new query named qryMTString and using this SQL:
Select A.CardNum, Null as TempString
INTO tblTempString
FROM qryResults as A
Group By A.CardNum;

Create a new query named qryUpdString and using this SQL:
UPDATE tblTempString as A INNER JOIN qryResults as B ON A.CardNum = B.CardNum SET A.TempString = IIf(IsNull(A.TempString),B.CompNumb, ", " & B.CompNumb);

Create a new query named qryCombineString and using this SQL:
Select A.CardNum, A.FName, A.LName, A.SchoolName, B.TempString as CompNumb
FROM qryResults as A INNER JOIN tblTempString as B on A.CardNum = B.CardNum
GROUP BY A.CardNum, A.FName, A.LName, A.SchoolName, B.TempString;

To run this use the following code:
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMTString"
DoCmd.OpenQuery "qryUpdString"
DoCmd.OpenQuery "qryCombineString"
DoCmd.SetWarnings True

Let me know how this works. I had no data to test it out.


Bob Scriver