manage many to many relationship

[Guest]

I am reaching this Microsoft Access problem that I can’t solve. Can anyone
help?
Example table data:
ID Project Name
1 prj1 Name1
2 prj2 Name1
3 prj1 Name2
4 prj3 Name2
5 prj4 Name1
6 prj2 Name3
… … …
I like the report output to be:
Project Name
Prj1 Name1, Name2
Prj2 Name1, Name3
Prj3 Name2
Prj4 Name1
… …

Thank you,
May-G

 

[Duane Hookom]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Guest]

Thank you Duane, this will help if the data file is small. What if the table
contains large information? Is there any other way other than search for the
whole file each time? Thank you!

 

[Duane Hookom]

Performance will be an issue with large amounts of records. Do you really
need to perform this against the entire table?

There is a SQL only method that might work but requires a temporary table
for appends/updates.

Since this is a report question, have you considered a main report based on
Project and a multi-column subreport with the names?

 

[Guest]

I did consider to multi columns except that I don’t know how to get there.
My report is combining two reports like what you suggest: “Main report based
on project†and subreport with the names. My original outcome was:

Projects name
Prj1 name1
name2
Prj2 name1
name3
prj3 name2
prj4 name1
name6
name10
name25
… …
If I can show the report to be the following, it will be very helpful
Projects Name
Prj1 name1 name2
Prj2 name1 name3
Prj3 name2
Prj4 name1 name6 name10 name25
Thank you for the quick respond

 

[Guest]

Never mind, it works now. Thank you very much!

Performance will be an issue with large amounts of records. Do you really
need to perform this against the entire table?

There is a SQL only method that might work but requires a temporary table
for appends/updates.

Since this is a report question, have you considered a main report based on
Project and a multi-column subreport with the names?