Crosstab Question

[jasonphillipstx]

I have data in a table like the following
fldClass fldPerson fldJob
Class1 John Lights
Class1 Sue Camera
Class1 Jack Sound
Class2 Fred Lights
Class2 Amy Camera
Class2 Arnold Sound
Class3 Henry Lights
Class3 Todd Camera
Class3 Sally Sound


What I wnat is something of a crosstab like

Class1 Class2 Class3
Lights John Fred Henry
Camera Sue Amy Todd
Sound Jack Arnold Sally

I cannot seem to get it to work. I welcome suggestions.

Thanks

 

[PHV]

TRANSFORM First(fldPerson) AS Person
SELECT fldJob
FROM yourTable
GROUP BY fldJob
PIVOT fldClass;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jasonphillipstx]

that almost did what I needed. The problem is that many times more than 1 Person are assigned the same Job. It only chooses the first record in Person. I need it to return all of them. I may need a nested query or something.

 

[PHV]

more than 1 Person are assigned the same Job
So, I suggest this:
TRANSFORM First(fldJob) AS Job
SELECT fldPerson
FROM yourTable
GROUP BY fldPerson
PIVOT fldClass;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jasonphillipstx]

I am sorry for not explaining this up front. But also, some Persons have more than 1 Job as well. Here is the work around I did. I started with your first crosstab but modified the data in my table such that I have Jobs like

Lights1
Lights2
Camera1
Camera2
Camera3

This works. Thanks for your help.

 

[dhookom]

You could have used the generic concatenate function found at

http://www.tek-tips.com/faqs.cfm?fid=4233

to combine multiple people per job.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]