Week No in a query, part 2

[Djbell]

Hi All

I posted a topic a few days ago asking how to create a week number from a date based upon a specific startdate and I got the following code which worked superbly, thanks for the code Sumitdev.

(DateDiff('ww',DateValue(DLookUp("[Weekstart]","week1start")),[APPLYDATE])+1)

I now have a part 2 query.. There will be occasions where several weeks will be left out, can i get the query to automatically insert week no's that are missing cause there is no data for them?

Regards

Douglas Bell

 

[rac2]

You could create a table CalendarWeeks which has one column, week_number, and 53 rows for the numbers 1 to 53.

Then create a query that yields incomplete data and includes a column with the number of the week, WeekNo.

Then create a third query JOINing these on the number of the week. Use a LEFT JOIN and you will obtain at least one row for each week.

For example, a base table with a DATE column named Starting_From. The first query which codes the date as the number of the week.

SELECT Collection_ID, 
       Starting_From, 
       DatePart("ww",[Starting_From]) AS WeekNo, 
       Starting_From AS OrderDate
FROM tblBaseRatesAndCommisions;

Save this query and call it GiveMeAWeek.


The second query yields at least one row for every week, more if there are more rows for the week in tblBaseRatesAndCommisions.

SELECT GiveMeAWeek.Collection_ID, 
       GiveMeAWeek.Starting_From, 
       GiveMeAWeek.WeekNo, 
       GiveMeAWeek.OrderDate,
       CalendarWeeks.week_number 
FROM CalendarWeeks LEFT JOIN GiveMeAWeek ON CalendarWeeks.week_number = GiveMeAWeek.WeekNo;

This is an example of a general requirement to show results for each category of a classification scheme, even when there are no observations in some categories. Make a table for the complete set and LEFT JOIN it with the table witht the observations or activities. Often such a table is ready at hand when it is used as a code table or a master list. For example, States or Customers. This example treats the number of the week in the year as a category of dates.