Reporting concatenated list from join table entry 1

[Firesmith]

I am using MS Access 2007. I have two tables and a join table between them. I want to create a report for one of the tables that lists the names of the entries in the other table related to it by the join table between them. For each row in the table (i.e., each row in the report), I want one field (column entry) to contain a concatenated list of the names of all related entries of the other table (by looking at the associated join table containing the IDs).

For example, suppose that I have the following three tables with the following rows and column entries:

tblA (A_ID, A_Name):
A1_ID, A1_Name
A2_ID, A2_Name
A3_ID, A3_Name

tblB (B_ID, B_Name):
B1_ID, B1_Name
B2_ID, B2_Name
B3_ID, B3_Name

join table tblAB (A_ID, B_ID):
A1_ID, B1_ID
A2_ID, B1_ID
A2_ID, B2_ID
A3_ID, B1_ID
A3_ID, B2_ID
A3_ID, B3_ID

I want the report for table tblA to have the following three columns and rows, whereby the concatenated lists of B names in the report are in alphabetical order (not by B_ID or join class ID):

A1_ID, A1_Name, (B1_Name)
A2_ID, A2_Name, (B1_Name;B2_Name)
A3_ID, A3_Name, (B1_Name;B2_Name;B3_Name)

This is the problem I need to solve quite a few times on my project so I need both the code and where to put it. (If I have it working once, I can repeat the solution by myself in the other places). I will also need to solve the identical problem with three classes with one join class among them, but with any luck I should be able to extend the solution from two tables to three by myself.

 

[dhookom]

Try this code faq701-4233.

Duane
Hook'D on Access
MS Access MVP

 

[Firesmith]

I have since found another almost identical concatenation procedure, and I got it to work in the case where there is no join table (a situation that came up several times). However, I have many cases where there is a join table as described in the initial post. Can anyone tell me how to address this case where three tables are related. I know I will need a concatenation program somewhere along the line but not sure about the code/SQL that needs to be around it.

 

[PHV]

I got it to work in the case where there is no join table
Did you try to use a JOIN in the SQL string ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Firesmith]

PHV,
Didn't know you could do that. What is the code to make it work without using the join table?

 

[PHV]

{i]What is the code[/i]
What is your actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886