crosstab report

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

Hi,

How can I create a report that is based on a crosstab query where the user
enters a date variable?

Eg.
Crosstab is based on a date range and is then formatted so that the columns
show "mm/yyyy"

If the user enters date range 1/1/02 - 3/3/02 then only three columns will
show

If the user enters 1/1/02 - 31/12/03 then 24 columns will show

Is this possible or do I have to limit the date range that can be selected
for eg '2003' or '2002' or a range of dates that only span 1 year?
 
I would create a report or reports that have a static amount of columns.
Then, use a text box on a form (Forms!frmA!txtEndDate) to get only the
ending date. Change your column heading in the crosstab to something like:
ColHead:"Mth" & DateDiff("m", [YourDateField],Forms!frmA!txtEndDate)
If your report has 12 columns, set the column headings property of the
crosstab to:
"Mth0","Mth1",..."Mth11"
Mth0 will be the month of the ending date and Mth11 will be 11 months prior.
You will also need to select Query->Parameters and enter
Forms!frmA!txtEndDate Date/Time

In your report, you can create column labels with text boxes
=DateAdd("m",-0, Forms!frmA!txtEndDate)
=DateAdd("m",-1, Forms!frmA!txtEndDate)
=DateAdd("m",-11, Forms!frmA!txtEndDate)
 
Thanks
I have tried to change the query to

ColHead: "Mth" &
DateDiff("m",[InvoiceDate],[forms]![frmSalesDates]![txtDateEnd])

but when trying to run the query it comes up with the error:

The microsofte Jet Database Engine does not recognize
'[forms]![frmSalesDates]![txtDateEnd])' as a valid field name or expression.

What am I doing wrong?

Al

Duane Hookom said:
I would create a report or reports that have a static amount of columns.
Then, use a text box on a form (Forms!frmA!txtEndDate) to get only the
ending date. Change your column heading in the crosstab to something like:
ColHead:"Mth" & DateDiff("m", [YourDateField],Forms!frmA!txtEndDate)
If your report has 12 columns, set the column headings property of the
crosstab to:
"Mth0","Mth1",..."Mth11"
Mth0 will be the month of the ending date and Mth11 will be 11 months prior.
You will also need to select Query->Parameters and enter
Forms!frmA!txtEndDate Date/Time

In your report, you can create column labels with text boxes
=DateAdd("m",-0, Forms!frmA!txtEndDate)
=DateAdd("m",-1, Forms!frmA!txtEndDate)
=DateAdd("m",-11, Forms!frmA!txtEndDate)

--
Duane Hookom
MS Access MVP


Newbie said:
Hi,

How can I create a report that is based on a crosstab query where the user
enters a date variable?

Eg.
Crosstab is based on a date range and is then formatted so that the columns
show "mm/yyyy"

If the user enters date range 1/1/02 - 3/3/02 then only three columns will
show

If the user enters 1/1/02 - 31/12/03 then 24 columns will show

Is this possible or do I have to limit the date range that can be selected
for eg '2003' or '2002' or a range of dates that only span 1 year?
Click to expand...
Click to expand...
 
You missed the step from my original reply:
You will also need to select Query->Parameters and enter
Forms!frmA!txtEndDate Date/Time
In your case this would be:
[forms]![frmSalesDates]![txtDateEnd] Date/Time

--
Duane Hookom
MS Access MVP


Newbie said:
Thanks
I have tried to change the query to

ColHead: "Mth" &
DateDiff("m",[InvoiceDate],[forms]![frmSalesDates]![txtDateEnd])

but when trying to run the query it comes up with the error:

The microsofte Jet Database Engine does not recognize
'[forms]![frmSalesDates]![txtDateEnd])' as a valid field name or expression.

What am I doing wrong?

Al

Duane Hookom said:
I would create a report or reports that have a static amount of columns.
Then, use a text box on a form (Forms!frmA!txtEndDate) to get only the
ending date. Change your column heading in the crosstab to something like:
ColHead:"Mth" & DateDiff("m", [YourDateField],Forms!frmA!txtEndDate)
If your report has 12 columns, set the column headings property of the
crosstab to:
"Mth0","Mth1",..."Mth11"
Mth0 will be the month of the ending date and Mth11 will be 11 months prior.
You will also need to select Query->Parameters and enter
Forms!frmA!txtEndDate Date/Time

In your report, you can create column labels with text boxes
=DateAdd("m",-0, Forms!frmA!txtEndDate)
=DateAdd("m",-1, Forms!frmA!txtEndDate)
=DateAdd("m",-11, Forms!frmA!txtEndDate)

--
Duane Hookom
MS Access MVP


Newbie said:
Hi,

How can I create a report that is based on a crosstab query where the user
enters a date variable?

Eg.
Crosstab is based on a date range and is then formatted so that the columns
show "mm/yyyy"

If the user enters date range 1/1/02 - 3/3/02 then only three columns will
show

If the user enters 1/1/02 - 31/12/03 then 24 columns will show

Is this possible or do I have to limit the date range that can be selected
for eg '2003' or '2002' or a range of dates that only span 1 year?
Click to expand...
Click to expand...
Click to expand...
 
Thanks it appears to be working now except . . . .

On the column headings of the report it is showing:

31/5/03 30/4/03 etc
is it possible to have Jan, Feb Mar etc?

Thanks
Al
Duane Hookom said:
You missed the step from my original reply:
You will also need to select Query->Parameters and enter
Forms!frmA!txtEndDate Date/Time
In your case this would be:
[forms]![frmSalesDates]![txtDateEnd] Date/Time

--
Duane Hookom
MS Access MVP


Newbie said:
Thanks
I have tried to change the query to

ColHead: "Mth" &
DateDiff("m",[InvoiceDate],[forms]![frmSalesDates]![txtDateEnd])

but when trying to run the query it comes up with the error:

The microsofte Jet Database Engine does not recognize
'[forms]![frmSalesDates]![txtDateEnd])' as a valid field name or expression.

What am I doing wrong?

Al

Duane Hookom said:
I would create a report or reports that have a static amount of columns.
Then, use a text box on a form (Forms!frmA!txtEndDate) to get only the
ending date. Change your column heading in the crosstab to something like:
ColHead:"Mth" & DateDiff("m", [YourDateField],Forms!frmA!txtEndDate)
If your report has 12 columns, set the column headings property of the
crosstab to:
"Mth0","Mth1",..."Mth11"
Mth0 will be the month of the ending date and Mth11 will be 11 months prior.
You will also need to select Query->Parameters and enter
Forms!frmA!txtEndDate Date/Time

In your report, you can create column labels with text boxes
=DateAdd("m",-0, Forms!frmA!txtEndDate)
=DateAdd("m",-1, Forms!frmA!txtEndDate)
=DateAdd("m",-11, Forms!frmA!txtEndDate)

--
Duane Hookom
MS Access MVP


Hi,

How can I create a report that is based on a crosstab query where
Click to expand...
the
user
enters a date variable?

Eg.
Crosstab is based on a date range and is then formatted so that the
columns
show "mm/yyyy"

If the user enters date range 1/1/02 - 3/3/02 then only three
Click to expand...
columns
will
show

If the user enters 1/1/02 - 31/12/03 then 24 columns will show

Is this possible or do I have to limit the date range that can be selected
for eg '2003' or '2002' or a range of dates that only span 1 year?
Click to expand...
Click to expand...
Click to expand...
 
Then try a summed column with the expression:
TotSales: Abs( Sum( ([InvoiceDate]
=DateAdd("yyyy", -1,forms!frmSalesDates!txtDateEnd) AND [InvoiceDate]
Click to expand...
<=forms!frmSalesDates!txtDateEnd) * NetSalesValue) )
Expression
Row Heading

The "InvoiceDate >= and <=" expression will evaluate to either True/-1 or
False/0. Multiply this times your NetSalesValue results in either 0 for
dates outside the range or -NetSalesValue for dates inside the range. The
result is summed and then converted to a positive with Abs().

--
Duane Hookom
MS Access MVP


Newbie said:
I am not sure what you mean - I have a total sales column

My problem is that the [Total Sales] is the sum of everything before the end
date rather than limited to the 12 columns that are displayed. How can I
change this?

Thanks again for all your help

Query so far . . . . .
PARAMETERS [forms]![frmSalesDates]![txtDateEnd] DateTime;
TRANSFORM Sum(Round([NetSalesValue],0)) AS Expr2
SELECT dbo_ArTrnDetail.Customer, ArCustomer.Name, dbo_ArTrnDetail.StockCode,
Sum(Round([NetSalesValue],0)) AS [Total Sales]
FROM dbo_ArTrnDetail INNER JOIN ArCustomer ON dbo_ArTrnDetail.Customer =
ArCustomer.Customer
WHERE (((dbo_ArTrnDetail.StockCode)<>""))
GROUP BY dbo_ArTrnDetail.Customer, ArCustomer.Name,
dbo_ArTrnDetail.StockCode
PIVOT "Mth" & DateDiff("m",[InvoiceDate],forms!frmSalesDates!txtDateEnd) In("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth1
0","Mth11");

Duane Hookom said:
You can add another Row Heading that sums your total sales column.

--
Duane Hookom
MS Access MVP


only the
user columns
will
Click to expand...
Click to expand...
 
Back
Top