Create a comma-separated row from multiple rows of the same column?

Question

I have an access table like this

ID | UserName | CarBrand ------------------------- 0 Peter VW 1 Peter Ferrari 2 Mike Audi 3 Peter Dodge 4 Heidi BMW 5 Heidi Ford 

I need the names from the CarBrand field as a comma separated list for the report.

Is there a way (without VB, perhaps using the COALESCE alternative?) To create such a comma-separated string without the Name: part?

 Peter: VW, Ferrari, Dodge Mike: Audi Heidi: BMW, Ford 

As for the report, are there any other ways to do this, possibly using expressions in the report?

Answer 1

You cannot do this - this is access without VBA. Coalesce does not exist, but you can write a UDF that has some features, for example http://allenbrowne.com/func-concat.html However, once you use UDF, the request is no longer viable outside of Access.

Answer 2

Create a master report with a unique list of usernames.

Create an additional report with a list of usernames and their car brands. Attach the forms to the username. When developing a subform, use 4-5 columns, which are printed horizontally. You can have a CarBrand field, including the formula, for example = [CarBrand] and "," (sorry that the latter has an unnecessary comma. They will be spaced evenly and will be split to a new line if a specific user has more brands than can match your report (which will be very difficult to do if you just create one big comma.).

No VBA.

Answer 3

If you don’t need each individual value in a comma-separated list, but say only until the first 3 or 4 or so, then Access has a pure SQL solution.

I am working on a database for a non-profit organization that has Tutors and Classes. For most classes there are only 1-2 teachers. To display goals, I cannot list more than 2 or 3 in any case, so I don’t worry about trimming emissions with the help of 5 or more teachers.

This will capture 3 teachers for each class with the lowest Tutor identifiers.

 Select JTC1.ClassID, Min(JTC1.TID1) as TutorID1, Min(JTC1.TID2) as TutorID2, Min(JTC1.TID3) as TutorID3 from ( Select distinct TC1.ClassID, TC1.TutorID as TID1, TC2.TutorID as TID2, TC3.TutorID as TID3 from (( Classes C Left Join TutorClasses TC1 on C.ClassID = TC1.ClassID) Left Join TutorClasses TC2 on TC1.ClassID = TC2.ClassID and TC1.TutorID < TC2.TutorID ) Left Join TutorClasses TC3 on TC2.ClassID = TC3.ClassID and TC2.TutorID < TC3.TutorID ) as JTC1 Group by JTC1.ClassID 

Obviously, it takes 1 extra step (not shown) to combine the three columns into 1.