Limiting sum calculations for a crosstab report

[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

 

[Guest]

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

 

[Duane Hookom]

If you can't figure this out, post your entire SQL view.