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]

Try not to use "absolute" column headings for dates. You could possibly use "relative" dates. For instance:
[tt][blue]
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
[/blue][/tt]
You want to show 5 days of sales in columns of a crosstab report.

Set the data type of the control with:
[tt][blue]
Query->Parameters
Forms!frmA!txtEndDate Date/Time
[/blue][/tt]

Change the Column Headings expression to:
[tt][blue]
ColHead: "Day" & [YourDateField],Forms!frmA!txtEndDate)
[/blue][/tt]
Set your query's Column Headings property to:
[tt][blue]
"Day0", "Day1", "Day2", "Day3", "Day4"
[/blue][/tt]

Build your report based on these "relative" days. If you need column labels in your report, use text boxes with control sources of:
[tt][blue]
=DateAdd("d",0,Forms!frmA!txtEndDate)
=DateAdd("d",-1,Forms!frmA!txtEndDate)
=DateAdd("d",-2,Forms!frmA!txtEndDate)
=DateAdd("d",-3,Forms!frmA!txtEndDate)
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[girvich36]

Duane

Actually that was not my question. All I want to do is to get the "mth0" column headings to reflect the actual months and year based on the crosstab query parameters. I have been struggling for 5 days to get the columns headings to show "January 2011", etc...Here is a copy of the original sql for the crosstab query without your formula. The format "mmmm yyyy" for the "Mth0" column headings is what I am trying to do

PARAMETERS [Forms]![Date Range]![Start Date] DateTime, [Forms]![Date Range]![End Date] DateTime;
TRANSFORM Sum([Month Appraisal Order Types Complete].[# of Orders]) AS [SumOf# of Orders]
SELECT [Month Appraisal Order Types Complete].[Order Types], [Month Appraisal Order Types Complete].[Task Job Manager], Sum([Month Appraisal Order Types Complete].[# of Orders]) AS Totals, Avg([Month Appraisal Order Types Complete].[# of Orders]) AS [AVG]
FROM tblMonth INNER JOIN [Month Appraisal Order Types Complete] ON tblMonth.[Month Date] = [Month Appraisal Order Types Complete].[Month Complete]
WHERE ((([Month Appraisal Order Types Complete].[Month Complete]) Between [Forms]![Date Range]![Start Date] And [Forms]![Date Range]![End Date]))
GROUP BY [Month Appraisal Order Types Complete].[Order Types], [Month Appraisal Order Types Complete].[Task Job Manager]
PIVOT [Month Appraisal Order Types Complete].[Month Complete];

 

[dhookom]

I thought you wanted to get the records for "the past 5 days".

Why do you want the record source query to return the headings like "January 2011"? Don't you simply want the report to display "January 2011"? If the query column headings are actual dates, you will need to modify the design of the query and/or report to accommodate the next year or other time period.

Duane
Hook'D on Access
MS Access MVP

 

[girvich36]

Duane

How in the world can I do this if the actual crosstab query is based on month dates -- see my sql for review. I can generate the crosstab query using the month dates but when I try to generate a report from this crosstab query, none of the fields are available.

Chris

 

[girvich36]

Wow I actually asked a question no one knows the answer to? unbelievable

 

[dhookom]

Don't pivot on the month. Pivot on the number of month difference between the [Month Complete] and [End Date]. This allows you to specify a static list of columns for your report. As the [End Date] changes, the "window" of months changes. The report has headings that are relative to the [End Date] so everything works beautifully.

Duane
Hook'D on Access
MS Access MVP

 

[girvich36]

Duane

not sure what you mean by this -- could you show me the sql query that would handle this? I understand the static number but I need the month as column headings in the report.

 

[dhookom]

All the information was explained in the FAQ. What is the data type of [Month Complete]? Is it a month number or date or what?

If [Month Complete] is actually a date field, try a query like:

PARAMETERS [Forms]![Date Range]![End Date] DateTime;
TRANSFORM Sum([Month Appraisal Order Types Complete].[# of Orders]) AS [SumOf# of Orders]
SELECT [Month Appraisal Order Types Complete].[Order Types],
 [Month Appraisal Order Types Complete].[Task Job Manager], 
 Sum([Month Appraisal Order Types Complete].[# of Orders]) AS Totals,
 Avg([Month Appraisal Order Types Complete].[# of Orders]) AS [AVG]
FROM tblMonth INNER JOIN [Month Appraisal Order Types Complete] ON 
  tblMonth.[Month Date] = [Month Appraisal Order Types Complete].[Month Complete]
GROUP BY [Month Appraisal Order Types Complete].[Order Types],
 [Month Appraisal Order Types Complete].[Task Job Manager]
PIVOT "Mth" & DateDiff("m",[Month Complete],[Forms]![Date Range]![End Date])
IN ("Mth0","Mth1","Mth2","Mth3",...etc...,"Mth11") ;

Duane
Hook'D on Access
MS Access MVP

 

[girvich36]

Duane

Still didn't work -- this is really bugging me that I cannot do a report from a crosstab query. The Month Complete Date is a date field (04/01/2012, 05/01/2012, 06/01/2012, etc...). The error I am getting are the Syntax error (missing operator) in query expression "Mth" & DateDiff("m",[Moth Complete],[Forms]![Date Range]![End Date], IN ("Mth0","Mth1","Mth2","Mth3",etc...,"Mth11")

 

[dhookom]

It won't work if you don't spell field names correctly and replace a ) with a ,.

Duane
Hook'D on Access
MS Access MVP

 

[girvich36]

Still didnt work -- do I need to add your column header formula into the SQL query? really need to figure this out

 

[dhookom]

Please share your SQL of the crosstab query and explain what you mean by "didn't work".

Duane
Hook'D on Access
MS Access MVP

 

[girvich36]

My original SQL query:

PARAMETERS [Forms]![Date Range]![Start Date] DateTime, [Forms]![Date Range]![End Date] DateTime;
TRANSFORM Sum([Month Appraisal Order Types Complete].[# of Orders]) AS [SumOf# of Orders]
SELECT [Month Appraisal Order Types Complete].[Order Types], [Month Appraisal Order Types Complete].[Task Job Manager], Sum([Month Appraisal Order Types Complete].[# of Orders]) AS Totals, Avg([Month Appraisal Order Types Complete].[# of Orders]) AS [AVG]
FROM tblMonth INNER JOIN [Month Appraisal Order Types Complete] ON tblMonth.[Month Date] = [Month Appraisal Order Types Complete].[Month Complete]
WHERE ((([Month Appraisal Order Types Complete].[Month Complete]) Between [Forms]![Date Range]![Start Date] And [Forms]![Date Range]![End Date]))
GROUP BY [Month Appraisal Order Types Complete].[Order Types], [Month Appraisal Order Types Complete].[Task Job Manager]
PIVOT [Month Appraisal Order Types Complete].[Month Complete];

Your SQL query

PARAMETERS [Forms]![Date Range]![End Date] DateTime;
TRANSFORM Sum([Month Appraisal Order Types Complete].[# of Orders]) AS [SumOf# of Orders]
SELECT [Month Appraisal Order Types Complete].[Order Types],
[Month Appraisal Order Types Complete].[Task Job Manager],
Sum([Month Appraisal Order Types Complete].[# of Orders]) AS Totals,
Avg([Month Appraisal Order Types Complete].[# of Orders]) AS [AVG]
FROM tblMonth INNER JOIN [Month Appraisal Order Types Complete] ON
tblMonth.[Month Date] = [Month Appraisal Order Types Complete].[Month Complete]
GROUP BY [Month Appraisal Order Types Complete].[Order Types],
[Month Appraisal Order Types Complete].[Task Job Manager]
PIVOT "Mth" & DateDiff("m",[Month Complete],[Forms]![Date Range]![End Date])
IN ("Mth0","Mth1","Mth2","Mth3",...etc...,"Mth11") ;

The error I get is the following:

Syntax error (missing operator) in query expression "Mth" & DataDiff("m",[Month Complete],[Forms]![Date Range]![End Date]) IN (Mth0","Mth1","Mth2","Mth3",...etc...,"Mth11")

 

[dhookom]

I expected you to understand you needed to complete

IN ("Mth0","Mth1","Mth2","Mth3",[red]...etc...[/red],"Mth11")

with

IN ("Mth0","Mth1","Mth2","Mth3",[red]"Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth10"[/red],"Mth11")

Duane
Hook'D on Access
MS Access MVP

 

[girvich36]

Got that figured out but now my error is Undefined Function "DataDiff" in expression

 

[dhookom]

If you used "DataDiff" rather than "DateDiff" you will get this message. Please when you get an error, just go back and read my postings or search Help on date functions.

Duane
Hook'D on Access
MS Access MVP

 

[girvich36]

Well excuse me for trying to get this resolved and I was asking an expert. By the way, the column headings aren't right on the crosstab query anyways (comes out as Mth0 instead of 05/01/2012, which is what I wanted to see as the column heading on the query). I guess I will move on and try to find a different source. Thanks for trying

 

[dhookom]

You hav never justified why you need to display the date as a column heading. You should only need to display the date value in the report which is exactly what the FAQ is trying to show you. The primary point of the FAQ is to create the exact same column headings to feed into your report regardless of the date range.

Duane
Hook'D on Access
MS Access MVP

 

[girvich36]

The date format for the column headings is to show a trailing 12 month volume history for each appraiser (ex. From 5/1/2011 to 5/1/2012, may 2011 to may 2012