Crosstab Queries

[Account Tech-Safety]

I put a post in the General section regarding this, but realy feel the Query is where I should focus. Here is what I need to accomplish: I have a report 3 times a school year that compares the same period from the previous school year. My comparison data is a count of incidents in 10 different categories. I have about 17 sites who report data to me. The trimester reports are YTD figures, so 2nd Trimester has data from 1st/2nd, and the 3rd Trimester has all three.

I have set up a crosstab query and report and it was working, but somehow during other fixings, I have now messed up my crosstab reports. It has to do with the school years. When I made the original crosstab, I apparently had the data for one particular school year in a field identified by the year. i.e. 2000-2001, now I have a field in my table called "SchoolYear" and I use a drop down combo box to select the correct year.

This is a brand new database and I want to do it correctly so that I can easily work through the school year changes, and the trimesters. The only data that I have input so far is 138 records for the school year 2000-2001, which this year will be my Last year's comparison data. The 138 records are for the 1st Trimester only.

Seems to me like I should bew using some criteria for the school year in the crosstab query, and report, but I am not sure how to write it.

Can someone tell me if I am on the right track? Thanks

 

[Jon.N]

From what you have said I'm not sure if I am on the right track as it were, so please forgive me if I'm way out.

It reads like you had your query working but then you changed some fields, perhaps you changed the field types, e.g. from a text box to a combo box and you have also changed the field names.

Make sure the field names you have in your query are the same as the field names that are in the table. Also, if you have any expressions in your query check them.

If you have changed the data type in any of the fields you may need to rebuild your query.

One further hint. I very rarely use crosstab queries. Assuming you are using a recent version of MS Office you may want to look at pivot tables. Access has a superb Wizard for creating Excel pivot tables and pivot charts. The pivot table Wizard is an option that becomes available when you use the new forms wizard within Access.

 

[Account Tech-Safety]

Thanks for the hint. I did go back and work through my fields and corrected the problem. I have necer used pivot tables, but I will take a look at them, because the cross tab so far is the only thing that worked for the report I needed.