Aargh… This is driving me crazy. I’m trying to do a crosstab query – “client funding by month/year.” However, the crosstab wizard wants to combine all the month data regardless of year – i.e. January 2000, 2001 and 2002 all get lumped under one “January” heading. No, no, no – who the heck would want to see their data this way? I want it broken out by month AND year.
BTW, I have to use month/year as the column headings, not row headings (which does allow grouping) because of the amount of data.
Easy. You start by un-installing the crosstab query wizard …
Seriously, I don’t know of any way to get the wizard to do this. But once it has built your query, you can go into the query in design view and change the formula for the query column that the crosstab is using to build the column headers, so that it outputs “Year/Month” instead of just “Month”.
(The reason you want “Year/Month” instead of “Month/Year” is left as an exercise for the student. )
It sounds like you have complicated matters a lot, and Access is not very forgiving when it comes to complicated entries.
I am not even all that sure I understand exactly what you are trying to describe:
Jan 00 Jan 01 Jan 02
Feb 00 Feb 01 Feb 02
If your entries look like the above, I would work on simplifying the columns - ie:
2000 2001 2002
Jan
Feb
March
It might not seem like much of a change, but the clarity for both the programmer, and for Access wizards to understand, is very helpful.
Also, I don’t know how much data entry has already been done, but if it is not a huge amount, you could still cut and paste and get this in order.
I don’t claim to be an expert, but I have created 5 Access databases here at work from scratch, and if I have learned anything, it is that you have to be very, very clear with data entry fields if you ever hope to be able to make the data useful in reports.
Have you tried logging onto the MS Access help boards and getting feedback from “the pros”? I found them to be very helpful.
Another BIG suggestion that almost always works for me…turn off the computer and walk away. Sometimes the worst problems disappear with a good night’s sleep and fresh eyeballs in the morning.
Run the “whiz-ard” and choose the date field for the column heading. Access will let you choose various formats - pick “month” (this part you have done before)
after the wizard completes the crosstab, go into design view. The column heading field will look something like:
Expr1: Format([OrderDate],“mmm”)
Change “mmm” to “yyyy/mm”
you would think this would be enough, but noooooo! … still in design view, right click in any blank area in the top part of the query. Choose properties. You should now see a property sheet called “query properties”
The Column Headings will have “Jan”, “Feb”, etc. Blank this out
)
).