Date-based crosstabs

[PaulA]

Greetings-

I am trying to set up a crosstab query with columns being formated as months and that will utilize start date and end date parameters that can start in one year and end in another year.

I have found that when doing this the month aggregration will include all data from the particualar month from all years (such as June data from both 2001 and 2002).

I want flexibility in my report in the start date and end date parameters.

Any thoughts how to accomplish this?

Thanks.

 

[thouston]

Try adding an extra row heading "year" which is the year part of your date. Then they'll be separated out in the crosstab. I don't know if that's the format you wanted but at least the years aren't mixed together any more...

 

[PaulA]

Thanks for your response--

I was hoping that such seperation could be reflected in the column headings--for example, show a range of 7/01 to 6/02 across the columns.

 

[thouston]

OK, how about this...

Supposing your date column is called "dateval"

I assume that at present you are using something like group:month([dateval]) to group the columns of your crosstab table.

Instead, try group: Month([dateval]) & "-" & Year([dateval]).

This will give a composite group named by month and year. Use the original field dateval with a "where" clause to call the parameter for the start and end limits for your dates.

Hope that's clear - it's easier to do than explain!

PS. Probably best to do Year before Month - otherwise the order gets a bit eccentric.

 

[Pat Hartman]

You should swap the year and month otherwise, your column headings will be in order by month rather than year/month. So, if you select Nov, 00 - Feb, 02, your column headings will be

01/01 01/02 02/01 02/02 03/01 04/01, etc. rather than
11/00 12/00 01/01 02/01 03/01 04/01, etc.