Sum of totals - horizontal query problem 1

[deltaair]

I have a table that has a column with shift, and a number of different fields, like say VAL1, VAL2, VAL3....VAL10.

I built a query that groups the totals for each "VAL" by shift. This gives me all the information but in horizontal format. However, I need to build a report in vertical format, that has the column headings as AM Shift, PM Shift. and then beneath those enlists all the different values / totals for each VAL, enlisting the name of the field in each row, and then totals for AM , PM Shift respectively.

How do I do this ?

 

[TheAceMan1]

How are ya deltaair . . .

It sounds like you need a [blue]Crosstab[/blue] query!

See Ya! . . . . . .

 

[deltaair]

A crosstab query doesn't allow as many fields, any further suggestion on how to exactly set it up ?

 

[deltaair]

Let me define the problem further,

I have a table with say Date, Shift (AM or PM), and then say Field1, Field2, Field3, ...,,,Field10, Each Field is a numeric value entered for each shift and each day. I need to make a report, that for all the dates in that table it gives me three columns, 1st - Field, 2nd-AM, 3rd PM. Under Field it lists all the NAMES of the fields, i.e Field1...Field10, and under AM and PM it gives the respective totals, for these fields.

How should I make a query for this. A crosstab query requires a row heading, and my row heading is the field names of the 10 numerical value fields...

 

[PHV]

Something like this ?
SELECT [Date],'Field1' AS Field,Sum(IIf(Shift='AM',Field1,0) AS AM,Sum(IIf(Shift='PM',Field1,0) AS PM
FROM yourTable GROUP BY [Date]
UNION SELECT [Date],'Field2' AS Field,Sum(IIf(Shift='AM',Field2,0) AS AM,Sum(IIf(Shift='PM',Field2,0) AS PM
FROM yourTable GROUP BY [Date]
...
UNION SELECT [Date],'Field10' AS Field,Sum(IIf(Shift='AM',Field10,0) AS AM,Sum(IIf(Shift='PM',Field10,0) AS PM
FROM yourTable GROUP BY [Date]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[deltaair]

Thanks for your help,

However, some of the fields were in currency format and they lost their number format when i put it in the query and then the report. Anyway to ensure that the data formats are preserved ?

 

[PHV]

Use the CCur function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886