Query to Transpose Multiple Rows to Columns

  • Thread starter Thread starter j1een
  • Start date Start date
J

j1een

Hi. I am a complete novice in using Access and am trying to figure
out how to transpose rows to columns in Access. The data I am trying
to transpose are the output of a query that performed calculations.
Here's the layout:

SubUnit Type T1 T2 T3
1A E 0.2 0.4 0.1
1A D 0.3 0.2 0.7
1B E 0.5 0.4 0.2
1C D 0.1 0.2 0.1

Here's how I need them:

Subunit Time E D
1A T1 0.2 0.3
1A T2 0.4 0.2
1A T3 0.1 0.7
1B T1 0.5 0.1
1B T2 0.4 0.2
1B T3 0.2 0.1


I realize that the existing layout may not be ideal, but try as I
might, I cannot figure a way to rearrange it given the calculations
that 1st need to be made to get it to this point. I've searched
online and think that a union and cross tab query might work but my
attempts thus far have been unsuccessful. As it often happens, I've
been asked to create this database for work without having any
official training in Access, so I'd greatly appreciate any help!
 
I'm not sure what happened to SubUnits 1C. I assume this should have been 1B.

Start by creating a normalizing query [quniToTranspose] with SQL like:
SELECT ToTranspose.SubUnit, ToTranspose.Type, ToTranspose.T1 AS TheValue,
"T1" AS TheTime
FROM ToTranspose
UNION ALL
SELECT ToTranspose.SubUnit, ToTranspose.Type, ToTranspose.T2, "T2"
FROM ToTranspose
UNION ALL
SELECT ToTranspose.SubUnit, ToTranspose.Type, ToTranspose.T3, "T3"
FROM ToTranspose;

Then create a crosstab with the SQL of:
TRANSFORM First(quniToTranspose.TheValue) AS FirstOfTheValue
SELECT quniToTranspose.SubUnit, quniToTranspose.TheTime
FROM quniToTranspose
GROUP BY quniToTranspose.SubUnit, quniToTranspose.TheTime
PIVOT quniToTranspose.Type;
 
I'm not sure what happened to SubUnits 1C. I assume this should have been1B.

Start by creating a normalizing query [quniToTranspose] with SQL like:
SELECT ToTranspose.SubUnit, ToTranspose.Type, ToTranspose.T1 AS TheValue,
"T1" AS TheTime
FROM ToTranspose
UNION ALL
SELECT ToTranspose.SubUnit, ToTranspose.Type, ToTranspose.T2, "T2"
FROM ToTranspose
UNION ALL
SELECT ToTranspose.SubUnit, ToTranspose.Type, ToTranspose.T3, "T3"
FROM ToTranspose;

Then create a crosstab with the SQL of:
TRANSFORM First(quniToTranspose.TheValue) AS FirstOfTheValue
SELECT quniToTranspose.SubUnit, quniToTranspose.TheTime
FROM quniToTranspose
GROUP BY quniToTranspose.SubUnit, quniToTranspose.TheTime
PIVOT quniToTranspose.Type;

--
Duane Hookom
Microsoft Access MVP

j1een said:
Hi.  I am a complete novice in using Access and am trying to figure
out how to transpose rows to columns in Access.  The data I am trying
to transpose are the output of a query that performed calculations.
Here's the layout:
Click to expand...
SubUnit     Type     T1     T2     T3
1A              E         0.2    0.4   0.1
1A              D         0.3    0.2   0.7
1B              E         0.5    0.4   0.2
1C              D         0.1    0.2   0.1
Click to expand...
Here's how I need them:
Click to expand...
Subunit Time   E       D
1A          T1    0.2    0.3
1A          T2    0.4    0.2
1A          T3    0.1    0.7
1B          T1    0.5    0.1
1B          T2    0.4    0.2
1B          T3    0.2    0.1
Click to expand...
I realize that the existing layout may not be ideal, but try as I
might, I cannot figure a way to rearrange it given the calculations
that 1st need to be made to get it to this point.  I've searched
online and think that a union and cross tab query might work but my
attempts thus far have been unsuccessful.  As it often happens, I've
been asked to create this database for work without having any
official training in Access, so I'd greatly appreciate any help!
Click to expand...
Click to expand...

Thank you so much! It worked like a charm!
 
Back
Top