Transpose rows to columns in Access.

[hatemma]

I would be grateful for help with creating a query that does the following:
transpose rows to columns and maintain the ID colume
Table
ID Date Amount Colour nature
2 15/10/04 20 9 N
2 17/03/06 12 6 H
1 15/10/04 15 8 N
1 17/03/06 65 5 L
1 3/9/08 98 63 N

Query Output
ID Character 15/10/2004 17/03/2006 03/09/2008
1 Amount 15 65 98
1 Colour 8 5 63
1 nature N L N
2 Amount 20 12
2 Colour 9 6
2 nature N H

Many thanks for your help

 

[Gina Whipp]

hatemma,

I want to say look at Crosstab queries and it may very well be the solution.
BUT it has a maximum column amount but I don't remember what it is and
depending on how many dates you have you could very well exceed them.
Perhaps if you could run your query with parameters which would keep you
within the maximum columns?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Gina Whipp]

Turns out the maximum is 255 so you should be good unless you are looking at
the whole year?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[KARL DEWEY]

First a union query then crosstab --
tbl_hatemma_union --
SELECT tbl_hatemma.ID, tbl_hatemma.Date, "Amount" AS [Character],
tbl_hatemma.Amount AS QTY
FROM tbl_hatemma
UNION ALL SELECT tbl_hatemma.ID, tbl_hatemma.Date, "Color" AS [Character],
tbl_hatemma.Color AS QTY
FROM tbl_hatemma
UNION ALL SELECT tbl_hatemma.ID, tbl_hatemma.Date, "Nature" AS [Character],
tbl_hatemma.Nature AS QTY
FROM tbl_hatemma;

TRANSFORM First(tbl_hatemma_union.QTY) AS FirstOfQTY
SELECT tbl_hatemma_union.ID, tbl_hatemma_union.Character,
First(tbl_hatemma_union.QTY) AS [Total Of QTY]
FROM tbl_hatemma_union
GROUP BY tbl_hatemma_union.ID, tbl_hatemma_union.Character
PIVOT Format([Date],"Short Date");