how do I transpose a tabular report?

  • Thread starter Thread starter Aidan
  • Start date Start date
A

Aidan

I need to transpose my report in access - Columns in to rows and rows in to
columns. Can I do this on the report or on the query that the report data is
held
 
You can use a union query to transform multiple columns into rows. I have
heard this referred to as a "normalizing union query". To get this
transformed back in to columns, you can use a crosstab query.

For instance, if you have a budget table with fields:
tblBudget
===========
Category
Jan
Feb
Mar
Apr
May
-- etc --
You could normalize this with a union query like:
SELECT Category, 1 as Mth, Jan as BudgetAmt
FROM tblBudget
UNION ALL
SELECT Category, 2, Feb
FROM tblBudget
UNION ALL
SELECT Category, 3, Mar
FROM tblBudget
UNION ALL
SELECT Category, 4, Apr
FROM tblBudget
-- etc --

Then if you want the Mth as the Row Heading and each Category as the Column
Heading, you could use a crosstab.
 
I don't have a table. You have a table. I provided a sample based on what I
thought your structure might be and what you wanted to derive from the
structure.

If I guessed wrong (happens quite often), you need to provide your actual
table and field names, maybe some sample records, and desired final display.
 
hope this helps It is a sample of the query I need to transpose


CATEGORY Prior Cost Add Cost Total Cost
F&F 10.00 5.00 15.00
MV 15.00 20.00 25.00
Plant 25.00 30.00 35.00

Please note there are more columns and rows I jsut need to understand how to
get the categories accross the top and the cost columns as rows down the left
hand side

Thx

Aidan
 
Just like Duane said --
SELECT Aidan.CATEGORY, "Prior Cost" AS [Cost], Aidan.[Prior Cost] AS [Value]
FROM Aidan
UNION ALL SELECT Aidan.CATEGORY, "Add Cost" AS [Cost], Aidan.[Add Cost] AS
[Value]
FROM Aidan
UNION ALL SELECT Aidan.CATEGORY, "Total Cost" AS [Cost], Aidan.[Total Cost]
AS [Value]
FROM Aidan;

TRANSFORM Sum(Aidan_1.[Value]) AS SumOfValue
SELECT Aidan_1.[Cost], Sum(Aidan_1.[Value]) AS [Total Of Value]
FROM Aidan_1
GROUP BY Aidan_1.[Cost]
PIVOT Aidan_1.[CATEGORY];
 
Back
Top