counting by week

[pb21]

What I would like to do is get a count of the product sold and view it over a ten week period in this case week one starts 26th June.

Wk1, Wk2, Wk3 ....

x 10 5 15 etc
y 20 4 12




in the format above but I am not sure how to achieve this, I have the following:


SELECT COURSEBK.[COURSE-DSName], COURSEBK.From, COURSEBK.Description, Count(COURSEBK.[COURSE-DSName]) AS [CountOfCOURSE-DSName]
FROM COURSEBK
GROUP BY COURSEBK.[COURSE-DSName], COURSEBK.From, COURSEBK.Description
HAVING (((COURSEBK.From)>=#6/26/2005# And (COURSEBK.From)<=DateAdd("ww",+10,"26/06/05")))
ORDER BY COURSEBK.From;

(the above is working despite access changing the date format between the hashes)


regards in advance foir guidance

 

[pb21]

Counting by week with crosstab query

I followed your advice and have a query thats counting by week. I tried adding the a parameter to the date in the form [enter summer start date] but it wont run in a crosstab query like that it says it doesnt recoginse the field name. It does work if i put the date in.

also how do I get it to count week 27 as 1. week 26/06/05 should be one in our business model and not 27 that way it should count from 1 to 10.

regards

peter

query:

TRANSFORM Count(COURSEBK.[COURSE-DSName]) AS [CountOfCOURSE-DSName]
SELECT COURSEBK.[COURSE-DSName], Year([From]) AS [year], COURSEBK.Description
FROM COURSEBK
WHERE (((COURSEBK.From) Between #6/26/2005# And DateAdd("ww",+10,"26/6/05")))
GROUP BY COURSEBK.[COURSE-DSName], Year([From]), COURSEBK.Description
PIVOT "Wk " & DatePart("WW",[From],1,0);

 

[pb21]

I miss understood, having added the parameter it now grabs the date fine. How do I get it to start the week count from 1 to 10 starting from the date input?

 

[Jon K]

You can't change the week number headings in the crosstab query.

However, you can first use a query to select the necessary records and add a calculated field in the query to display the required week numbers. Then build a crosstab query from the first query.
.