Rolling Month in crosstab-like report

[JoeMiller]

I often use crosstab reports to pull data for reports on things like customer sales where you show the customer, product line, and then the sales going across the page left to right by month. The problem I have is I want to always show the latest month as the last column to the right. So if I run a report in May for the last twelve months it will show May as the rightmost column and April as the first, not January first and December twelfth.

What I've done in the past is create 12 different versions of the report and setup ways to figure out which version to use based on a date somewhere (be it on a form, or in the data). I would much prefer to make one report that automatically figures out what data goes where and change the control sources on the fly. Has anybody done something like this and have an example or maybe some ideas how to do it? Any help is greatly appreciated! Joe Miller
joe.miller@flotech.net

 

[MichaelRed]

JOe,

I have done it. The 'examples' are (literally) locked in the bank (where I was wne creating these). Hazy memory suggests that I "Kept" many more than the one years woth of data in the table. To get the most recent year of data, first do a select for the date range. Based DateAdd("d", -365, Now()), you get the years worth of data (Alternative: DateAdd("m", -12, Now()). Either method can include / exclude some records - depending on details (leap year ... ). You will need to carefully review the details of what you want, as there are NUMEROUS variations in the desired end result (e.g. if Now ~ May 10, 2001 - do you want all records from May 10, 2000, all records from May 2000, records from April 2000 ... )


Next, do a simple calc that assigns the value of 12 to the "Rightmost" month and 1 the "LeftMost" one (? 12 - (month(Now) - [TransDate])).

Use this as the coll number and assign the fields to the text boxes on the form/report.

LOTS more in the detail, but thats the (cogito ergo sum) general approach.



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[raskew]

Don't have the exact date (sometime in 98/99), but this precise problem was covered in "Inside Microsoft Access".) Building it was tedious but I recall that it worked as advertised. Have it at work and will provide you the exact date / title tomorrow (Friday 11 May).

 

[Andrea001]

Raskew,
Were you ever able to find the issue that covered this problem in "Inside Microsoft Access?"
Thanks,
Andrea

 

[JoeMiller]

Andrea,

I wound building a solution myself in code. Basically it's code that builds a new query to select data out of your crosstabs with the months relabeled into the proper order. I have a sample database I can send if you're interested. Let me know your email address and I can send it over.

Joe Miller
joe.miller@flotech.net

 

[Andrea001]

Thanks Joe! I just sent you an e-mail.
Andrea