Limiting sum calculations for a crosstab report

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

Guest

Does anyone know how to limit total figures for a crosstab, either in at the
query stage or within the report? Basically, I only want my crosstab query
to produce product sales for the last six weeks, up to and including the week
number entered by the user into a form. I have created the column headings
for the crosstab query by entering the following formula:

"Wk" & ([Week]-[Forms]![frmEPOSReportDialogue]![txtEndWeek])

I then entered the following for the Row Heading:

Total: [SalesQuantity], with 'Sum' selected

Under Query Properties, I have entered the following into the Column
Headings field to restrict the number of weeks displayed:

"Wk0","Wk-1","Wk-2","Wk-3","Wk-4","Wk-5"

All of the six weekly figures then appear correctly, however the total sales
figures give figures for all weeks, rather than just forthe six weeks
intended.

Any help will be much appreciated.

Regards,

Jedster
 
Add in a where clause to you query limiting either the weeks or by date. Here
Is an example by date...

WHERE ((([a/r open].[sdate from]) Between [Forms]![ar yearly]![date from]
And [Forms]![ar yearly]![date to]))

HTH
Martin
 
Back
Top