I have two fields that I want to list by unique ClientID. I don't recall how to easily take a list of multiple records for each person and dump their data into one field separated by commas. I don't know how to write much code. I think I used to use a function in MS Acces.
ORIGINAL DATA: ClientId Type 70 Other 70 Kundalini 81 Other 81 Hatha 474 Hatha 474 Other
SELECT ClientID, Count(Type) as CountTypes, ConcatTypes(ClientID) AS Prim_Type FROM myTable GROUP BY ClientID;
Be sure to replace myTable in both the query and the code with your actual table name
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
Public Function ConcatTypes(myID as Long) as String
Dim db as Database, rs as Recordset
Set db=currentdb
Set rs=db.openrecordset("myTable",dbOpenDynaset)
rs.findfirst "ClientID = " & myID
Do While rs.ClientID = myID
ConcatTypes = ConcatTypes & rs.Type & ", "
rs.MoveNext
Loop
ConcatTypes = Left(ConcatTypes,Len(ConcatTypes)-2)
End Function
