Query Conversion

[razchip]

Good morning, I need some help laying some crosstab query data into a useable format. The data below is what I'm working with:

Month Area Hrs
Apr Prod 81.77
Aug Prod 2168.37
Aug Supv 397.61
Aug Unsc 506.95
Feb Prod 150.12
Feb Unsc 1.83
Jan Prod 223.90
Jan Supv 12.06
Jan Unsc 173.92

What I would like to obtain is:

Month Prod Supv UnSc Total
Jan 223.9 12.06 173.92 409.88
Feb 150.12 1.83 151.95

Does anyone have an idea what I can do, thanks for any suggestions.


Thanks for the help.
Greg

 

[JerryKlmns]

Create a crosstab query by using wizard?

 

[fneily]

There is only one problem with the Crosstab query - it'll sort the months Alphabetically. So you can do this (sloppy but it works):
Create another field in your table, say MonthNum. Then give the months the appropriate number Jan =1, Feb = 2, etc.
Create a crosstab query using MonthNum and Month as the row headings, Area as the column headings and Hours as the data with the Sum function. Run the query. Move the Total column to the far right.
Switch back to design view. On the criteria line of Month, put "Jan" Or "Feb". Run the query.
Now Right click the MonthNum heading and select Hide Columns. Bingo!

 

[razchip]

Thanks, worked great. I appreciate the help.

Thanks for the help.
Greg

 

[JerryKlmns]

I think that the sorting, could be solved by declaring the months on the Column Headings of the property sheet

 

[fneily]

Column Headings mean Headings for the Columns.
There's nothing on the property sheet for ROW Headings.

 

[JerryKlmns]

Ooops!

[small]Where is that edit button ??[/small]