Crosstab query with dates need to subtotal by month

[rb9999]

Hello, I have a database with building numbers and dates. I basically need to create a crosstab query with the building numbers being the rows and "JAN", "FEB", "MAR" etc being the column names. In each column I need the total of occurrences of a date falling in that month for that building.

For example, my table:

BLD# DATE
100 01/10/2004
100 02/02/2004
101 01/19/2004
101 01/10/2004
101 01/12/2004
101 02/05/2005

The query should produce this:

BLD JAN FEB MAR APR MAY ETC.....
100 1 1 0 0 0
101 3 2 0 0 0

Any tips or pointers appreciated.

 

[rb9999]

Actually, I made a mistake in the results...it should produce this:

BLD JAN FEB MAR APR MAY ETC.....
100 1 1 0 0 0
101 3 1 0 0 0

 

[PHV]

Play with Format([date field],'mmmyyyy')

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

 

[rb9999]

Yes, that did it. I knew it had to be farily easy.

I did this:

Expr1: Format([TrainDate],'mm/yyyy')

It puts the columns in numerical order. The Date are "counted" and is the VALUE.

Thanks very much.