Combining rows in query?

[rev]

Let me start by apologizing if this is dumb, I'm pretty new to Access and totally self taught. I've been searching for an answer for days (google) and I'm just not getting anywhere.



I have training data that shows a unique employee ID and a training date for each of the 4 courses they have either taken, or still need to take. I am retrieving the employee ID from the Training_Headcount table and the completion dates from the Training_Data table. Here is my query:

SELECT Training_Headcount.ID, IIf([Course Type Number]=50696650,[completion date]) AS 50696650, IIf([Course Type Number]=60124831,[completion date]) AS 60124831, IIf([Course Type Number]=50461876,[completion date]) AS 50461876, IIf([Course Type Number]=60242846,[completion date]) AS 60242846, IIf([Course Type Number]=51367268,[completion date]) AS 51367268
FROM Training_Data INNER JOIN Training_Headcount ON Training_Data.[Learner ID] = Training_Headcount.ID
ORDER BY Training_Headcount.ID;

Here's the problem, when I run the query I get a new row for each time an ID shows a completion date. (See attachment "what I got")

I would like to have a single row for each ID with an entry for their training dates going across. (See attachment "what I want")

Can anyone help me figure out how to accomplish this?

[alansidman]

This method may work for you. Look at it and try it.

http://www.datapigtechnologies.com/f.../crosstab.html

[rev]

Thanks Alan. That's the same process I'm following but the issue (I believe) is that my ID column returns results that are not unique. In the example on datapigtechnologies.com the values for segment (which would be ID for me) are unique and therefore are not returning multiple times down for each cross tab entry across the top.

I need to figure out if I can combine the appearance of identical ID's into a single row? Any ideas?