Field as column name.

[Michel Pauwels]

Hi,



My problem is that I have the following two tables (tblParticipants and
tblOrganisationWorkshops).



The layout of tblParticipants looks like this:

[ParticipantID] [Name]

1 Jantje

2 Anneke

3 Mieke

4 Thomaske



And tblOrganistationWorkshops is made like this:

[Id] [Participant] [Hour] [Workshop]

1 1 14 javascript

2 1 15 router

3 1 16 security

4 2 14 router

5 2 15 javascript

6 2 16 security

7 3 14 javascript

8 3 15 security

9 3 16 router

10 4 14 javascript

11 4 15 router

12 4 16 security



I want the result (query) to look like this:

[Name] [14] [15] [16]
Jantje javascript router security
Anneke router javascript security
..



Can anyone tell me how I should get the hours (from
tblOrganisationWorkshops) as the column name?
Thanks in advance.

 

[Guest]

The first query looks pretty but will not get all information if there are
two with same hours.

TRANSFORM First(tblOrganistationWorkshops.Workshop) AS FirstOfWorkshop
SELECT tblParticipants.Name
FROM tblParticipants LEFT JOIN tblOrganistationWorkshops ON
tblParticipants.ParticipantID = tblOrganistationWorkshops.Participant
GROUP BY tblParticipants.Name
PIVOT tblOrganistationWorkshops.Hour;


TRANSFORM First([ParticipantID] & " " & [Workshop]) AS Expr1
SELECT tblParticipants.Name
FROM tblParticipants LEFT JOIN tblOrganistationWorkshops ON
tblParticipants.ParticipantID = tblOrganistationWorkshops.Participant
GROUP BY tblParticipants.Name, [ParticipantID] & " " & [Workshop]
PIVOT tblOrganistationWorkshops.Hour;

 

[Michel Pauwels]

Thanks.