Is this possible

[SteveG]

I appologize if this is a repost. I wated over an hour but it never appeared.

I have an interesting delima. I have a crosstab query that displays
historical information for any 12 month period. It displays months as (01-12)
along the top and days (1-31) along the side the values are days taken off.

This query feeds into a report. It is the report I am having trouble with.

If I choose Jan 1 2007 thru Dec 31 2007 I would get the following:

01 02 03 04 - 12
01 3 0 1 3 5
02 2 2 1 0 5
03 0 1 1 2 0
04 0 0 1 0 0
-31 1 0 0 0
Total 6 3 4 5 10

The problem I have is if I choose July 1 2007 thru June 30 2008
the months still display as 01-12 on the report so the data is a little
mixed up.

Is there a way to always have the first month always =1 and the last month =
12

The result I am looking for is to have the data display like the followng
(values were copied from above):
07 08 09 - 05 06
01 3 0 1 3 5
02 2 2 1 0 5
03 0 1 1 2 0
04 0 0 1 0 0
-31 1 0 0 0
Total 6 3 4 5 10

Does this make sense? I dont want to have to modify the report I use each
time the query is run to make sure the months are in the correct order.

Thank you in advance for any assistance.
SteveG

 

[ceesdatabase]

Hello Steve,

One possible solution could be adding the year and month into one column
2007-10 2007-11 2007-12 2008-01 2008-02 etc

 

[John Spencer]

Since you are using the results of this crosstab query in a report, you can't
change the column "names". If you do the report will break when it does not
get the expected results.

What you can do is use relative names for the columns that are returned and
then use VBA to change the column labels in the report. Since I don't know how
you are supplying the date range or what your query looks like, I cannot give
more specific advice.

You might take a look at Duane Hookom's Dynamic crosstab solution at
http://www.access.hookom.net/Reports.htm

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[SteveG]

Thank you for the link to the website. I will check it out.