Viewing Data by Weeks

[rjvega]

I have a table which has various defects that may occur in an area. I want
to create a query which views the data by the week in the year (i.e. the
first week of the year is week 1, etc). My data set starts on June 3, 2007
which is week 23.

One of my problems is that there are some weeks where there are no defects,
and I want that to show as 0. So ultimately what I want to see is something
like:

Week Defects
23 1
24 0
25 0
26 4
27 5

Now, I've tried a few different methods after reading various posts here.
I've set it up using DatePart("ww", [DefectDate]), but it doesn't show the
weeks where there were zero defects. I've tried creating a table of dates
and left joining this to the date recorded on the defect, but I end up seeing
the same week number repeated 7 times before it goes to the next. I've
messed with sortings, but then I see the results starting from week 1 (I need
it to start at week 23). I've tried other things as well, but I want to see
what approach is recommended here.

Thanks!

 

[Marshall Barton]

I have a table which has various defects that may occur in an area. I want
to create a query which views the data by the week in the year (i.e. the
first week of the year is week 1, etc). My data set starts on June 3, 2007
which is week 23.

One of my problems is that there are some weeks where there are no defects,
and I want that to show as 0. So ultimately what I want to see is something
like:

Week Defects
23 1
24 0
25 0
26 4
27 5

Now, I've tried a few different methods after reading various posts here.
I've set it up using DatePart("ww", [DefectDate]), but it doesn't show the
weeks where there were zero defects. I've tried creating a table of dates
and left joining this to the date recorded on the defect, but I end up seeing
the same week number repeated 7 times before it goes to the next. I've
messed with sortings, but then I see the results starting from week 1 (I need
it to start at week 23).

I think you'll get better results if your dates table only
contained week numbers. Then you could use something like:

SELECT WeekNumbers.Week, Count(*) As Defects
FROM WeekNumbers LEFT JOIN yourtable
ON WeekNumbers.Week = DatePart("ww", yourtable.DefectDate)
WHERE WeekNumbers.Week >= [enter start week]
GROUP BY WeekNumbers.Week