Rolling crosstab reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to produce a rolling crosstab report to show the last 6 weeks'
product sales by week number (ie. if this week is week no. 41, the report
should show total product sales for weeks 35 to 40). Has anybody attempted
this and can they offer any advice as to what might be the best approach?

Any help or examples will be much appreciated.

Regards,

Jedster
 
This is a posting that I have used before that creates by month. You should
be able to apply this to weeks.
================
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)
...
This solution requires no code and will run fairly quickly.
================
 
Duane,

Thanks for your help. I used your reply as the basis for my solution which
now mostly works fine.

All I now need is a way to cater for when the week number criterion on the
form is set between 1 and 5. This is so that I can avoid having non-existent
week numbers such as "Week 0" or "Week -1", etc., erroneously appearing on
the report. Is there a method that I can use to prevent this from happening?
Also, there might be a way in which I could replace data for week numbers
earlier than 1 with data for week numbers up to 52 of the previous year,
although this may prove too laborious and is by no means an essential
requirement.

Thanks in advance.

Jedster


Duane Hookom said:
This is a posting that I have used before that creates by month. You should
be able to apply this to weeks.
================
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)
...
This solution requires no code and will run fairly quickly.
================
--
Duane Hookom
MS Access MVP


Jedster said:
I am trying to produce a rolling crosstab report to show the last 6 weeks'
product sales by week number (ie. if this week is week no. 41, the report
should show total product sales for weeks 35 to 40). Has anybody attempted
this and can they offer any advice as to what might be the best approach?

Any help or examples will be much appreciated.

Regards,

Jedster
Click to expand...
Click to expand...
 
I'm sorry, I don't understand your question(s). The report should be
designed for a specific number of time periods. Are you asking how to hide
one or more time periods/columns in the report?

--
Duane Hookom
MS Access MVP
--

Jedster said:
Duane,

Thanks for your help. I used your reply as the basis for my solution
which
now mostly works fine.

All I now need is a way to cater for when the week number criterion on the
form is set between 1 and 5. This is so that I can avoid having
non-existent
week numbers such as "Week 0" or "Week -1", etc., erroneously appearing on
the report. Is there a method that I can use to prevent this from
happening?
Also, there might be a way in which I could replace data for week numbers
earlier than 1 with data for week numbers up to 52 of the previous year,
although this may prove too laborious and is by no means an essential
requirement.

Thanks in advance.

Jedster


Duane Hookom said:
This is a posting that I have used before that creates by month. You
should
be able to apply this to weeks.
================
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)
...
This solution requires no code and will run fairly quickly.
================
--
Duane Hookom
MS Access MVP


Jedster said:
I am trying to produce a rolling crosstab report to show the last 6
weeks'
product sales by week number (ie. if this week is week no. 41, the
report
should show total product sales for weeks 35 to 40). Has anybody attempted
this and can they offer any advice as to what might be the best
approach?

Any help or examples will be much appreciated.

Regards,

Jedster
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane,

My report is a rolling crosstab report where the user first enters a week
number onto a form. The relevant sales for that week, and the previous 5,
are then generated within a series of columns in the report.

My problem was that when a user enters a week number of, say, '3', the
report will then produce columns for week numbers -2, -1 and 0, which don't
actually exist, and week numbers 1, 2 and 3 which do exist.

To provisionally get around this problem, I have now included variations of
the following formula to hide the unwanted columns:

=IIf([Forms]![frmEPOSReportDialogue]![txtEndWeek]-5<1,"","Wk " &
[Forms]![frmEPOSReportDialogue]![txtEndWeek]-5)

I am now wondering whether it would be possible to replace these hidden
columns with sales for the latter week numbers of the previous year instead,
eg. 50, 51 and 52.

Regards,

Jedster


Duane Hookom said:
I'm sorry, I don't understand your question(s). The report should be
designed for a specific number of time periods. Are you asking how to hide
one or more time periods/columns in the report?

--
Duane Hookom
MS Access MVP
--

Jedster said:
Duane,

Thanks for your help. I used your reply as the basis for my solution
which
now mostly works fine.

All I now need is a way to cater for when the week number criterion on the
form is set between 1 and 5. This is so that I can avoid having
non-existent
week numbers such as "Week 0" or "Week -1", etc., erroneously appearing on
the report. Is there a method that I can use to prevent this from
happening?
Also, there might be a way in which I could replace data for week numbers
earlier than 1 with data for week numbers up to 52 of the previous year,
although this may prove too laborious and is by no means an essential
requirement.

Thanks in advance.

Jedster


Duane Hookom said:
This is a posting that I have used before that creates by month. You
should
be able to apply this to weeks.
================
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)
...
This solution requires no code and will run fairly quickly.
================
--
Duane Hookom
MS Access MVP


I am trying to produce a rolling crosstab report to show the last 6
weeks'
product sales by week number (ie. if this week is week no. 41, the
report
should show total product sales for weeks 35 to 40). Has anybody
attempted
this and can they offer any advice as to what might be the best
approach?

Any help or examples will be much appreciated.

Regards,

Jedster
Click to expand...
Click to expand...
Click to expand...
 
Have your users enter a date as my suggested solution uses. If they must
enter a week number then use a hidden text box to calculate a date in the
week.

--
Duane Hookom
MS Access MVP


Jedster said:
Hi Duane,

My report is a rolling crosstab report where the user first enters a week
number onto a form. The relevant sales for that week, and the previous 5,
are then generated within a series of columns in the report.

My problem was that when a user enters a week number of, say, '3', the
report will then produce columns for week numbers -2, -1 and 0, which don't
actually exist, and week numbers 1, 2 and 3 which do exist.

To provisionally get around this problem, I have now included variations of
the following formula to hide the unwanted columns:

=IIf([Forms]![frmEPOSReportDialogue]![txtEndWeek]-5<1,"","Wk " &
[Forms]![frmEPOSReportDialogue]![txtEndWeek]-5)

I am now wondering whether it would be possible to replace these hidden
columns with sales for the latter week numbers of the previous year instead,
eg. 50, 51 and 52.

Regards,

Jedster


Duane Hookom said:
I'm sorry, I don't understand your question(s). The report should be
designed for a specific number of time periods. Are you asking how to hide
one or more time periods/columns in the report?

--
Duane Hookom
MS Access MVP
--

Jedster said:
Duane,

Thanks for your help. I used your reply as the basis for my solution
which
now mostly works fine.

All I now need is a way to cater for when the week number criterion on the
form is set between 1 and 5. This is so that I can avoid having
non-existent
week numbers such as "Week 0" or "Week -1", etc., erroneously appearing on
the report. Is there a method that I can use to prevent this from
happening?
Also, there might be a way in which I could replace data for week numbers
earlier than 1 with data for week numbers up to 52 of the previous year,
although this may prove too laborious and is by no means an essential
requirement.

Thanks in advance.

Jedster


:

This is a posting that I have used before that creates by month. You
should
be able to apply this to weeks.
================
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)
...
This solution requires no code and will run fairly quickly.
================
--
Duane Hookom
MS Access MVP


I am trying to produce a rolling crosstab report to show the last 6
weeks'
product sales by week number (ie. if this week is week no. 41, the
report
should show total product sales for weeks 35 to 40). Has anybody
attempted
this and can they offer any advice as to what might be the best
approach?

Any help or examples will be much appreciated.

Regards,

Jedster
Click to expand...
Click to expand...
Click to expand...
 
Back
Top