Transposing data in a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In the underlying query of my report I have fields for
Group, Trait 1, Trait 2 and Trait 3. My report displays
data so that there is one row for each record (Group)
as shown below.

Gp Trt1 Trt2 Trt3
A 15 5 7
B 20 6 8
C 19 2 6

I want change the report so that there would be one
column for each group and one row for each trait.
Gp A B C
Trt1 15 20 19
Trt2 5 6 2
Trt3 7 8 6

This is the same same as transposing data in Excel.
How do I do this in Access?
 
You first normalize your data with a union query
SELECT Gp, "Trt1" as Trait, Trt1 as TrtValue
FROM qryUnderlying
UNION ALL
SELECT Gp, "Trt2", Trt2
FROM qryUnderlying
UNION ALL
SELECT Gp, "Trt3", Trt3
FROM qryUnderlying;

You can then create a crosstab based on the union query where Trait is the
Row Heading, Gp is the Column heading, and TrtValue is the Value.
 
Back
Top