report with totals for last sizx months and average

  • Thread starter Thread starter Rick Kane via AccessMonster.com
  • Start date Start date
R

Rick Kane via AccessMonster.com

I've been trying to create a report giving total expenditure by category for the last six months, with a column for the six-month average (including in the average any month with $0 expended for the given category). That is:

5 months 4 months etc. this 6-mmm
ago ago 3 ... 2... 1... month avg.
column
headings
(for ex.): Nov Dec Jan Feb Mar Apr Avg

caegrory1 sum sum sum sum sum sum avg
category2
etc.

This must be a common report, and is probably easy, but I'm new to Access and stymied -- I've tried several approaches, some no doubt convoluted. I could not figure out an expression for my cross tab query attempts that would limit my columns to the last six months instead of all 12 months and give the right column headings and include the average column I want ... I won't amuse you with all my other approaches.

(I've been working mostly from a query that gsathers my data from underlying tables into the mere three fields needed: date (short mm/dd/yy), expenditureamount, and category. Each row/record in the query is one expenditure on a specific day for a specific category. There can be more than one expenditure for the same category on the same day (the differences are not relevant for this report). Seems like it should be so easy!)

I am very grateful fer any help ... thanks! ...... Rick

*****************************************
* This message was posted via http://www.accessmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=8f150b28571c49e188e4f14b3fc3b695
*****************************************
 
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


Rick Kane via AccessMonster.com said:
I've been trying to create a report giving total expenditure by category
Click to expand...
for the last six months, with a column for the six-month average (including
in the average any month with $0 expended for the given category). That is:
5 months 4 months etc. this 6-mmm
ago ago 3 ... 2... 1... month avg.
column
headings
(for ex.): Nov Dec Jan Feb Mar Apr Avg

caegrory1 sum sum sum sum sum sum avg
category2
etc.

This must be a common report, and is probably easy, but I'm new to Access
Click to expand...
and stymied -- I've tried several approaches, some no doubt convoluted. I
could not figure out an expression for my cross tab query attempts that
would limit my columns to the last six months instead of all 12 months and
give the right column headings and include the average column I want ... I
won't amuse you with all my other approaches.
(I've been working mostly from a query that gsathers my data from
Click to expand...
underlying tables into the mere three fields needed: date (short mm/dd/yy),
expenditureamount, and category. Each row/record in the query is one
expenditure on a specific day for a specific category. There can be more
than one expenditure for the same category on the same day (the differences
are not relevant for this report). Seems like it should be so easy!)
 
Duane -- thank you, very much! On my learning curve I am trying to not not use "absolute" column headings. First msytery: the column headings property in the crosstab query does not seem to accept an expression.

(I wasn't working from a form, so tried using today's date in place of what you suggested for enddate --forms!frmA!txtenddate-- but I also tried some simple expressions other than mere strings like "jun","jul" for the column headings property, no luck -- what am I doing wrong?)

thanks again ... making progress, Rick

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/uwe/Forum.aspx/access-reports/14096
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=28a2be1964d54c24a1f6a02b395d2068
*****************************************
 
Back
Top