Access 2003 Using one column to return multiple values

I need the sql for the following which has to work on access 2003.

I have 2 tables

List1 and Lookup1

List1 contains user_name and role_description
Lookup1 contains role_id and role_descrition.

There can be multiple roles for a user
I need to produce a list in the format.

user_name, role_id,role_id,role_id..etc..
eg.
FRED,1,2,4,6,8

The List1 table contains
FRED,role1
FRED,role2
FRED,role3
JOHN,role2
JOHN,role4

The lookup contains
1, role1
2, role2
3, role3
4, role4

The output needs to look like

FRED,1,2,3
JOHN,2,4

ericdavidsoncaAsked:
Who is Participating?
 
Patrick MatthewsCommented:
BTW, the syntax using DConcat from http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html



SELECT l.user_name, DConcat("[role_id]", "[Lookup1]", "[role_description] = '" & l.[role_description] & "'") AS Roles
FROM List1 l
GROUP BY l.user_name

Open in new window

0
 
GRayLCommented:
TRANSFORM First(b.ID)
SELECT a.Name FROM List1 a INNER JOIN Lookup1 b ON a.role_description = b.role_description
GROUP BY a.Name
PIVOT a.role_description;
0
 
ericdavidsoncaAuthor Commented:
Hi GRayL

I needed to be clearer in my example.

The format should be.
Column1       Column2
FRED            1,2,3
JOHN            3,4

where all the role_id's are all in column 2 in asceding role_id order
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
GRayLCommented:
Then I suggest you give this article by Patrick Mathews a read.  The function does what you want.
0
 
GRayLCommented:
Sorry Patrick, thanks for the recovery.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.