transpose report

  • Thread starter Thread starter Souris
  • Start date Start date
S

Souris

I have data table like following

Emp No Activity Date A1Value A2Value A3Value A4Value
1 3/20/2008 10 15 10 15
1 3/21/2008 5 10 1 2


I would like have a report like following


Emp No:1

Activity/Date 3/20/2008 3/21/208 3/22/2008

Activity 1 10 5
Activity 2 15 10
Activity 3 10 1



Dose crosstab query help to do so?
If not, any query example i can do above report?

Your information is great appreciated,
 
Yes, that's what a Crosstab Query is intended for.

Larry Linson
Microsoft Access MVP
 
First you will have to Normalize your data with a union query. Then you can
use the UNION query as the source for a crosstab query.

You can't build a union query in the design view, you must use the SQL view
to do so. THe Union query would look like:

SELECT [Emp no], [Activity Date], A1Value as TheValue
FROM [Your Table]
UNION ALL
SELECT [Emp no], [Activity Date], A2Value as TheValue
FROM [Your Table]
UNION ALL
SELECT [Emp no], [Activity Date], A3Value as TheValue
FROM [Your Table]
UNION ALL
SELECT [Emp no], [Activity Date], A4Value as TheValue
FROM [Your Table]

Once you have that you could build a crosstab query to return EmpNo,
TheValue, and the Activity dates. Turning that into a report is a bit more
complex since the Activity Date columns will vary as you add new dates or
apply criteria against the date field.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks millions,

John Spencer said:
First you will have to Normalize your data with a union query. Then you can
use the UNION query as the source for a crosstab query.

You can't build a union query in the design view, you must use the SQL view
to do so. THe Union query would look like:

SELECT [Emp no], [Activity Date], A1Value as TheValue
FROM [Your Table]
UNION ALL
SELECT [Emp no], [Activity Date], A2Value as TheValue
FROM [Your Table]
UNION ALL
SELECT [Emp no], [Activity Date], A3Value as TheValue
FROM [Your Table]
UNION ALL
SELECT [Emp no], [Activity Date], A4Value as TheValue
FROM [Your Table]

Once you have that you could build a crosstab query to return EmpNo,
TheValue, and the Activity dates. Turning that into a report is a bit more
complex since the Activity Date columns will vary as you add new dates or
apply criteria against the date field.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Souris said:
I have data table like following

Emp No Activity Date A1Value A2Value A3Value A4Value
1 3/20/2008 10 15 10 15
1 3/21/2008 5 10 1 2


I would like have a report like following


Emp No:1

Activity/Date 3/20/2008 3/21/208 3/22/2008

Activity 1 10 5
Activity 2 15 10
Activity 3 10 1



Dose crosstab query help to do so?
If not, any query example i can do above report?

Your information is great appreciated,
Click to expand...
Click to expand...
 
Back
Top