Pivot Table (kind of)

[fitshase]

I don't know if this should go in the reports section or the queries section but here goes:-

I have a simple table which tracks dates for various projects on different geographical sites (the test data is over 600 records).

I would like to have a report which lists months down the left and has several of the fields as column headings then insert the number of times that milestone has been met in that month:-

e.g., if there are 33 sites which have designs produced in March 2012 (i.e., there are 33 dates in the database in march 2012 for the field "Designs Produced" then under the column "Designs Produced" and on the row "March" under group 2012 it would show "33".

I cannot for the life of me work out how to do it! Any pointers?

 

[plog]

My gut wants me to tell you to properly normalize your data, but I'll hold off for now. Could you post some sample data (including field names) from your table and then what you would like the output to be on your report based on that sample data?

 

[Pat Hartman]

Try using the crosstab query wizard. It may build what you need. It "know" about dates and will ask how you want them summarized. One thing about the crosstab is that it limits you to three columns to display to the left of the grid. If you need more than three then just open the query in design view after the wizard finishes and you can manually add additional columns.