One to many relationship query

[fezzik]

Table: User
Column(s): UserId

Table: Classes
Column(s): UserId, ClassId

User -> Classes is a one to many relationship.

In Access is it possible to create a query that outputs 1 record per UserId so that the output would look like:

UserId ClassId
102 2, 45, 6, 8
103 1, 44, 33
111 34, 2, 32

Since the ClassId can return multiple records the output would be comma deliminated and only return 1 record per UserId. Is this possible within an Access query? Also, if so, how would you construct the query that had another additional table with a one to many relationship?

Any assistance is appreciated!

Best, Fezzik

 

[KeithG]

You would have to create a custom vba function. Can you post an example for me and I will do the coding for you?

 

[fezzik]

Thanks for the quick response Keith!

I'm dealing with 6 tables. The 'User' table has a one to many relationship with 'Expertise', 'Collaboration', 'Sabbaticals' and 'Sponsors'.

Table: Users
Columns: User_ID, First_Name, Last_Name, Email

Table: Expertise
Columns: Expertise_ID, User_ID, Country_ID

Table: Collaboration
Columns: Collaboration_ID, User_ID, Country_ID

Table: Sabbaticals
Columns: Sabbatical_ID, User_ID, Country_ID

Table: Sponsors
Columns: Sponsor_ID, User_ID, Country_ID

Table: Countries
Columns: Country_ID, Country

I would like the result set to list 1 record per User_ID. The output fields are: First_Name, Last_Name, Email, Expertise(Country name deliminated), Collaboration(Country name deliminated), Sabbaticals(Country name deliminated) and Sponsors(country name deliminated). Note: The field 'Country' within the 'Countries' table is the country name.

Thank you for help! Let me know if you need additional information.

Best, Fezzik