Crosstab query with multiple column heandings? 1

[VBAguy22]

Hi
I have a table of this kind:

ID | Hair | Eyes | Shoe Size | Factor
------------------------------------------
1 | Blnd | Blue | 11 | 1.2
2 | Black | Green | 9 | 1.9
3 | Brown | Hazel | 10 | 2.1

etc

What I want to do is have a crosstab query, that will output ID as my row headings, and the information contained in Hair, Eyes, Shoe Size fields as column headings. So I will have the following coluns:

ID, Blnd, Black, Brown, Blue, Green, Hazel, 11, 9, 10...

The Factor field would go inside the query and will be repeated for every instance (one to many joint):
2.1 will be in the Brown, Hazel, 10 columns....

Is it possible?

 

[dhookom]

You would need to first create a union query like:
Select ID, Factor, "Hair" as Attribute, [Hair] as AttribValue
FROM [table of this kind]
UNION ALL
Select ID, Factor, "Eyes", [Eyes]
FROM [table of this kind]
UNION ALL
Select ID, Factor, "Shoe Size", [Shoe Size]
FROM [table of this kind];

Then create a crosstab based on your union query and set AttibuteValue as the Column Heading, ID as the Row Heading, and Factor as the Value.



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]