If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Transpose query result into report
Hi!
I have a query that return something like this (data has been simplify for the exemple) : Need Date Needed On Time Late Outstanding Performance 01/01/2008 10 8 1 1 80% 02/01/2008 10 5 4 1 50% .. .. .. I need to show it transpose on a report like this : 01/01/2008 02/01/2008 .... Needed 10 10 On Time 8 5 Late 1 4 Outstanding 1 1 Performance 80% 50% The report will always show 12 months (it is not always from January to December, it could be July to june, April to March... the user can select the period) Can't figure how to do it... do someone have ideas? Thx -- Yanick |
#2
|
|||
|
|||
Transpose query result into report
First us a union query to pull it together -- qryActionItems --
SELECT [Need Date], "Needed" AS [Action], [Needed] AS Action_Value FROM YourTable UNION ALL SELECT [Need Date], "On Time" AS [Action], [On Time] AS Action_Value FROM YourTable UNION ALL SELECT [Need Date], "Late" AS [Action], [Late] AS Action_Value FROM YourTable UNION ALL SELECT [Need Date], "Outstanding" AS [Action], [Outstanding] AS Action_Value FROM YourTable UNION ALL SELECT [Need Date], "Performance" AS [Action], [Performance] AS Action_Value FROM YourTable; Then us a crosstab query. -- Build a little, test a little. "Yanick" wrote: Hi! I have a query that return something like this (data has been simplify for the exemple) : Need Date Needed On Time Late Outstanding Performance 01/01/2008 10 8 1 1 80% 02/01/2008 10 5 4 1 50% . . . I need to show it transpose on a report like this : 01/01/2008 02/01/2008 .... Needed 10 10 On Time 8 5 Late 1 4 Outstanding 1 1 Performance 80% 50% The report will always show 12 months (it is not always from January to December, it could be July to june, April to March... the user can select the period) Can't figure how to do it... do someone have ideas? Thx -- Yanick |
Thread Tools | |
Display Modes | |
|
|