transpose row into column

  • Thread starter Thread starter Ann Chen
  • Start date Start date
A

Ann Chen

I would like to create a report, which shows variables as
row and records in columns, a kind of like 'TRANSPOSE'
function in EXCEL.
Can anyone could provide advice? Many thanks.

Ann
 
This can generally be done with a union query as the source for a crosstab
query.

If you provided some table structure(s) and sample records with the desired
final display, someone could be more specific.
 
ie. I can create report like this very easily

Date SBP DBP BMI
Date_1 100 73 20.6
Date_2 90 69 28
Date_3 95 70 25

*** Date, SBP, DBP and BMI are variables.

But how I can do this:

Date Date_1 Date_2 Date_3
SBP 100 90 95
DBP 73 69 70
BMI 20.6 28 25


Thank you.
 
Normalize your data first with a union query:
SELECT [Date] as TheDate, SBP as TheValue, "SBP" as TheCategory
FROM tblNoName
UNION ALL
SELECT [Date], DBP, "DBP"
FROM tblNoName
UNION ALL

SELECT [Date], BMI, "BMI"
FROM tblNoName;

Then use the union query as the source of a crosstab query that uses TheDate
as the Column Heading, TheCategory as the Row Heading, and Sum of TheValue
as the value.
 
Hi, Duane.

It works!!! Thanks.
I have 7 tables needed to be reported, and there are 20
variables in the largest one. Do you have any suggestions
for me to make this job easier?

Thank you.
Ann

-----Original Message-----
Normalize your data first with a union query:
SELECT [Date] as TheDate, SBP as TheValue, "SBP" as TheCategory
FROM tblNoName
UNION ALL
SELECT [Date], DBP, "DBP"
FROM tblNoName
UNION ALL

SELECT [Date], BMI, "BMI"
FROM tblNoName;

Then use the union query as the source of a crosstab query that uses TheDate
as the Column Heading, TheCategory as the Row Heading, and Sum of TheValue
as the value.

--
Duane Hookom
MS Access MVP


ie. I can create report like this very easily

Date SBP DBP BMI
Date_1 100 73 20.6
Date_2 90 69 28
Date_3 95 70 25

*** Date, SBP, DBP and BMI are variables.

But how I can do this:

Date Date_1 Date_2 Date_3
SBP 100 90 95
DBP 73 69 70
BMI 20.6 28 25


Thank you.


in
message
Click to expand...


.
Click to expand...
 
Normalize your tables so that you don't need union queries.

--
Duane Hookom
MS Access MVP
--

Ann Chen said:
Hi, Duane.

It works!!! Thanks.
I have 7 tables needed to be reported, and there are 20
variables in the largest one. Do you have any suggestions
for me to make this job easier?

Thank you.
Ann

-----Original Message-----
Normalize your data first with a union query:
SELECT [Date] as TheDate, SBP as TheValue, "SBP" as TheCategory
FROM tblNoName
UNION ALL
SELECT [Date], DBP, "DBP"
FROM tblNoName
UNION ALL

SELECT [Date], BMI, "BMI"
FROM tblNoName;

Then use the union query as the source of a crosstab query that uses TheDate
as the Column Heading, TheCategory as the Row Heading, and Sum of TheValue
as the value.

--
Duane Hookom
MS Access MVP


ie. I can create report like this very easily

Date SBP DBP BMI
Date_1 100 73 20.6
Date_2 90 69 28
Date_3 95 70 25

*** Date, SBP, DBP and BMI are variables.

But how I can do this:

Date Date_1 Date_2 Date_3
SBP 100 90 95
DBP 73 69 70
BMI 20.6 28 25


Thank you.



-----Original Message-----
This can generally be done with a union query as the
source for a crosstab
query.

If you provided some table structure(s) and sample
records with the desired
final display, someone could be more specific.

--
Duane Hookom
MS Access MVP


message
I would like to create a report, which shows variables as
row and records in columns, a kind of like 'TRANSPOSE'
function in EXCEL.
Can anyone could provide advice? Many thanks.

Ann


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top