croostab query with changing colums as a recordsource

[girvich36]

I am struggling with setting up a crosstab query as a recordsource for a report in Access for the past 5 days. I came across your solution -- see below which has converted the months as "mth0", "mth1", "mth2", etc... the problem I am having is getting the column header to reflect the "mmmm yyyy" format -- any suggestions on how I can get this fixed??? I am really desperate to get this fixed for a deadline.

Chris

Dynamic Monthly Crosstab Report
faq703-5466
Posted: 7 Oct 04 (Edited 22 Oct 07)

Try not to use "absolute" column headings for dates. You could possibly use "relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
You should be able to substitute other date intervals for months such as "q" for quarter or "d" for day.

This solution requires no code and will run fairly quickly.

 

[dhookom]

Why do you need to display the date in the query column headings? Isn't this a report question? Don't you need to only display the date as a heading in the report?

Your report is going to either require static fields or you will have to implement some solution to change the control sources of at least 12 text boxes in the report. The FAQ uses a method of 12 static columns in the query with column headings in the report that display a value based on [Forms]![Date Range]![End Date]. You change the date on the form and everything automatically changes.

Duane
Hook'D on Access
MS Access MVP

 

[girvich36]

Again this leads me back of having the date being the column header in the report. Since the report is now picking up the headers of "Mth0" how do i change this to reflect the month dates...seems to me there got to be a way to update it but everything i see online states that the format has to be defined back in the query

 

[dhookom]

Go back to your first post in this thread

Build your report based on these "relative" days. If you need column labels in your report, use text boxes with control sources of:

=DateAdd("d",0,Forms!frmA!txtEndDate)
=DateAdd("d",-1,Forms!frmA!txtEndDate)
=DateAdd("d",-2,Forms!frmA!txtEndDate)
=DateAdd("d",-3,Forms!frmA!txtEndDate)

Change the "d" to "m" for Month rather than Day. Also change the "Forms..." to match your ending date control on your form.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Actually I quoted myself. Your first post was more on target based on your needs.

Duane
Hook'D on Access
MS Access MVP

 

[girvich36]

Nevermind i seem to be going in circles with your helpful hints. If you did not know how to do it just say so. The column labels cannot be changed in the report. Thanks for trying

 

[dhookom]

If you don't want the column labels changed in the report, how would anyone reading the report know what dates the columns represent?

I assume you want your report to look like this:

Order Type   Task Job Master  May 2011    Jun 2011    Jul 2011    Aug 2011    Sep 2011 ...  Apr 2012
===========  ===============  ========    ========    ========    ========    ========      ========
XXXXX        XXXXX               504        302         111          222        319           493
XXXXX        XXXXX               231        311         804          112        200           499
XXXXX        XXXXX               869        333         630          466        408           231
XXXXX        XXXXX                99        486         200          240        411           763

This is exactly what the FAQ is designed to present to the user in a report.

Duane
Hook'D on Access
MS Access MVP

 

[girvich36]

Well its not working that way since all i get is the "Mth0" etc.. column headings. I cannot go into the report design view and change the headers since the query is showing "Mth0" using your formula

 

[dhookom]

Are you seeing values in the crosstab under the MthX headings?

If so, do the values you see under Mth0 correspond with the values from date/month [Forms]![Date Range]![End Date]?


Duane
Hook'D on Access
MS Access MVP

 

[girvich36]

Yes they are correct

 

[dhookom]

So, can you build a report that is bound to the columns from the crosstab? Place the MthX columns in the order you want them to appear left to right.

Add text boxes above the Mth columns with control sources as described:
[tt]
Build your report based on these "relative" months. If you need column labels in your report, use text boxes with control sources of:
=DateAdd("m",0,[Forms]![Date Range]![End Date])
=DateAdd("m",-1,[Forms]![Date Range]![End Date])
=DateAdd("m",-2,[Forms]![Date Range]![End Date])
=DateAdd("m",-3,[Forms]![Date Range]![End Date])
--- duplicate this for all 12 months ---
[/tt]

Duane
Hook'D on Access
MS Access MVP