information from Junction table combined into one field related to 1 record with a query

Question

I am having difficulty trying to write a query for this example database: 3 tables = Program Code, Work, Work Code. Program Code table has a many to many relationship with the Work table through the Work Code table. Table layout is as follows:

Work: ID,Employee ID, Type of work, description, hours
Work code: ID, Program Code, Work ID
Program Code: Program Code

Now an employee will complete a job but have to charge multiple codes for that job.This creates many records in the Work Code table. I want to create a query that will pull the employees ID, Type of work, description, hours and link all program codes used for that 1 job entry. I have a query that runs but it creates a line for each program code instead of 1 line with all the program codes in one field. This is Access front end with SQL backend.

This is what I have right now:

SELECT Work.ID, Work.[Employee ID], Work.[Type of work], Work.description, Work.hours, [Work Code].[Program Code]
FROM [Work Code] INNER JOIN [Work] ON [Work Code].[Work ID] = Work.ID;

But I would like it to have the Program codes in one field per Work ID.

Answer 1

The only solution I can think of is probably not what you're looking for -- using a GROUP BY statement.

SELECT Work.ID, Work.[Employee ID], Work.[Type of work], Work.description, 
Sum(Work.hours) as SumOfHours, [Work Code].[Program Code]
FROM [Work Code] INNER JOIN [Work] ON [Work Code].[Work ID] = Work.ID
GROUP BY Work.ID, Work.[Employee ID], 
Work.[Type of work], Work.description, [Work Code].[Program Code];

I'm not aware that there's any way in SQL to force multiple joined rows into a single field in a single row. When I need this kind of functionality I generally go with the really dirty hack of storing, in this case, the Program Codes in a comma delimmitted format inside the Work Table. I use code to get them there, usually at the time the Program Codes are being entered.

As a side note, I recommend you stop using spaces in your table names and field names. SQL is a lot easier to read and write without brackets on everything. Using table aliases is another handy way to make SQL more readable.

With a few changes your SQL could look like this instead:

SELECT w.ID, w.EmployeeID, w.WorkType, w.Description, 
Sum(w.hours) as SumOfHours, wc.ProgramCode
FROM WorkCode AS wc INNER JOIN Work AS w ON wc.WorkID = w.ID
GROUP BY w.ID, w.EmployeeID, 
w.WorkType, w.Description, wc.ProgramCode;