Transpose rows to columns in Access.

H

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
 
G

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
 
K

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");
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top