Count by Week Number

[bigal.nz]

Hi All,

I have this query for counting number of records per week:

SELECT DatePart("ww", [DateStart]) AS weekno, count(*)
FROM MAS
WHERE DateStart Between #11/01/2017# AND #01/30/2018#
GROUP By DatePart("ww", [DateStart]);

This works great and the results look like:

WEEKNO COUNT
49 5
50 6
51 3
52 5

But I would like to modify it to show the date of the first day of each week as a seperate column.

Also I would like to include future weeks (notice the report goes into the future) where the results will be 0.

Thanks in advance

-Al

 

[plog]

...where the results will be 0.

Actually there will be no 0 results. That's true for past weeks that have no corresponding data in MAS. If a week has no data, the only way to make it appear with a 0 is to create a whole new table for every week you want to report on.

To get the first day of the week you would use the DateAdd function (https://www.techonthenet.com/access/functions/date/dateadd.php). You would add the number of weeks generated by your existing DatePart function to 11/1/2017 (assuming its the very first day of the very first week).

 

[arnelgp]

you will need a Cartesian Query
to achieve what you want.

first create three tables:
zzDayTable
zzMonthTable
zzYearTable

1. zzDayTable
FieldName	FieldType
--------------------------------
ThisDay		Number (Byte)

2. zzMonthTable
FieldName	FieldType
--------------------------------
ThisMonth	Number (Byte)

3. zzYearTable
FieldName	FieldType
--------------------------------
ThisYear	Number (Integer)

fill zzDayTable with days in month (1-31).
fill zzMonthTable with month number (1-12).
fill zzYearTable with current year and future year (2017-2050).

create a query using the above 3 tables to get the First Date of each Week (see zzQryFirstDayOfWeeks).

on your table with [StartDate] field, create similar Query like in the sample DB (Query1).

create the Final query that Joins zzQryFirstDayOfWeeks to the query you made against your table (see qryFinalResult).

 

[Galaxiom]

A variant of arnelgps technique uses a single table with ten records in a Cartesian join. It relies on the fact that dates are stored as the number of days since 30/12/1899.

The four way Cartesian join concatenates the ten digits into ten thousand numbers. (Alias the table again and concatenate another digit if you need more numbers.)

Add 43000 to bring the numbers into a contemporary date range. (Adjust this to suit your needs.)

Return every seventh number using Integer divide by 7 then multiply by 7.
(The backslash is the integer divide operator which returns only integers.)

Add 2 to get the Monday date numbers. (Use a different number for other days.)

Convert to date.

DISTINCT to return only one of the seven repeats.