How many per week (1 Viewer)

[Lanason]

I have data table with a date field going back 2 years
How can I get a count of how many records per week
note:- some weeks may have no records and so I need to have a zero value

My objective is to do a graph showing all weeks

Thanks in advance

 

[Ranman256]

make a tWeek table, the Week StartDate, and EndDate, this has your defined weeks:
8/27/17, 9/2/17
9/3/17, 9/9/17

use this table in a query with your data table, qsWeeklyData,
do not join the 2,
this will limit your data by criteria (and put int the weekEnding field as a label:

select *, tWeek.EndDate as WeekEnding from tData,tWeek where tData.Date between tWeek.startDate and tWeek.EndDate

once this query runs, make another query base on this query to calculate your weekly results:
select * from qsWeeklyData