CrossTab Query

[Newbie]

Hi,

I want to be able to run a report that is based on a crosstab query.

The data I have is
Product, Qty and Due Date, Status

I want a query that will group by Product and by Week No. and sum the qty
required based on a variable of due date. i.e. it will report on everything
that is required up until the date entered and a Status = "N"
eg. if I enter a date of 3/6/04 I want the query to include anything
outstanding prior to this date where Status = "N"

The total column would be equal to the sum of the records shown
The final result would look something like

Product Wk1 wk2 wk3 . . . .Wk12
A 10 14 2
B 5 12 4

Thanks

 

[Michel Walsh]

Hi,

PARAMETERS parameterName DateTime ;
TRANSFORM SUM(Qty)
SELECT Product
FROM myTable
WHERE Status='n' AND [Due Date] <= parameterName
GROUP BY Product
PIVOT "wk" & DatePart("ww", [Due Date] )



should do. A crosstab query accepts parameter, but they are obliged to be
declared!



Hoping it may help,
Vanderghast, Access MVP