Presenting many to many relationships in reports

[EternalMyrtle]

Hello,

I am finding that I have to do very complex queries to get reports the way I want them due to all the junction tables in my database.

For example, I have a report of all of our employees by title and I want to be able to add the employee's credentials to the end of their name separated by commas (so "Contact Name, Credential 1, Credential 2", etc.).

The report is based on a query "qryCurrentEmployees", which is just contacts filtered.

All the employees' credentials are in a junction table called

tblContactsCredentials
ContactsCredsID
ContactID
CredentialID

When I add the tblContactsCredentials to qryCurrentEmployees, employees with more than one type of credential are listed more than once. I got around that by putting the credential information into the row source of the ContactID on the report rather than in the main query and using an unbound text box with a calculated expression but I can only show one credential for each contact. Some people have 3+. Is there some way to get this to work?

I feel like this is a recurring issue for me and I have not found an ideal way to resolve it.

Many thanks to any one willing to help.

 

[way2bord]

Use vba to loop through all criteria, creating a string, then set title = string.

 

[Pat Hartman]

You need to use subreports to show the Many-side data.

 

[EternalMyrtle]

Thanks to both of you for your replies. Way2bord, I have no idea how to execute that but will look into it.

Pat, I always use subforms to represent many to many relationships in my forms so this makes sense (wish I had thought of it myself as it would have saved me tons of time making crazy queries that I don't fully understand) but I still don't understand how I would get multiple credentials to appear in a single row after a person's name.

I will have to try it when I get into the office on Tuesday and see if I can get it to look the way I want it to look.

Thanks again!

 

[Pat Hartman]

If you have only a few credentials, you can probably use a multi-column subform. If that look doesn't work for you, you will need a function that accumulates the many-side data and places it into a single column. Call it from your query.

 

[EternalMyrtle]

Pat,

I tried using the subreport with multiple columns as you suggested but I cannot get the credentials to show up in a single row.

You wrote: "If that look doesn't work for you, you will need a function that accumulates the many-side data and places it into a single column. Call it from your query."

I believe that this is exactly what I need. I have searched but cannot find a function to do this. As for inventing it myself, my skills are just not there yet. Are you able to help?

Thank you!

 

[EternalMyrtle]

FYI: I found this post and plan to try to work through it but it requires using vba to loop through the recordset as way2bord suggested: http://www.access-programmers.co.uk/forums/showthread.php?t=173379

It seems like overkill to use all this code just to list a few credentials after a person's name.

If anyone can think of a simpler way to do this, I am all ears!

 

[EternalMyrtle]

I am reluctant to post this as "Solved" since I think my solution is probably not ideal but I got this to work using the ConcatRelated() module as presented in this tutorial:

http://allenbrowne.com/func-concat.html

It works well but I had to make 3 separate queries in order to do it since I was basing this off of a junction table with two FK fields and my SQL query writing skills are lacking.

I doubt this is the best or cleanest possible solution but it works for now.

I didn't try the former solution because I liked the idea of having this in a module so it can be called in other cases when it is needed.