Display of query result set

[Gill1978]

Hi,

I have a sql query (stored procedure) that returns a set of results as follows:

Date [Count Post Midday] [pre midday] Heldover
-------------------------------------------------------
1/07/2007 0 2 1
2/07/2007 0 0 0
3/07/2007 12 0 0
4/07/2007 8 9 7
5/07/2007 42 11 14

However I'd like to display the result set in an Access report as follows:

1/07/2007 2/07/2007 3/07/2007
[Count Post Midday] 0 0 12
[pre midday] 2 0 0
Heldover 1 0 0

Can anyone tell me how to flip round the results in Access to show this?

Thanks

Julie

 

[Remou]

it would be possible to do this with a series of crosstabs:

'tblZ_C1
TRANSFORM Sum(tblZ.[Count Post Midday]) AS [SumOfCount Post Midday]
SELECT "Count Post Midday" AS hd
FROM tblZ
GROUP BY "Count Post Midday"
PIVOT tblZ.DateX;

'tblZ_C2
TRANSFORM Sum(tblZ.[pre midday]) AS [SumOfpre midday]
SELECT "pre midday" AS hd
FROM tblZ
GROUP BY "pre midday"
PIVOT tblZ.DateX;

'tblZ_C3
TRANSFORM Sum(tblZ.Heldover) AS SumOfHeldover
SELECT "Heldover" AS hd
FROM tblZ
GROUP BY "Heldover"
PIVOT tblZ.DateX;

These can then be combined:

SELECT *
FROM tblZ_C1
UNION ALL SELECT *
FROM tblZ_C2
UNION ALL SELECT *
FROM tblZ_C3

However, it may be easier to do this at the server end.

 

[dhookom]

First, normalize your query with SQL like:
[tt]== quniNormalized =====
SELECT [Date], [Count Post Midday] as TheValue, "Count Post Midday" As RowHeading
FROM q
UNION ALL
SELECT [Date], [Pre Midday], "Pre Midday"
FROM q
UNION ALL
SELECT [Date], [Heldover], "Heldover"
FROM q
[/tt]
Then build a crosstab based on quniNormalized that uses RowHeading as the Row Heading, [Date] as the Column Heading, and TheValue as the Value.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]