Help with group by week query

[Angel69]

Hi,

I built the below query to count the number of ID's for each week. The problem is that if one month ends mid-week it is counting the next month's IDs in the first month. I need to count the number of IDs within each week by month. I have the first qryGroupbyWeek that assigns a week number to each date then the below query to aggregate. Any ideas how to revise? TIA!

SELECT qryGroupbyWeek.Week, qryGroupbyWeek.[Approved Date], qryGroupbyWeek.ID
FROM qryGroupbyWeek
WHERE (((qryGroupbyWeek.[Approved Date]) Between [start] And [end]))
ORDER BY qryGroupbyWeek.ID;

 

[pr2-eugin]

Could you also show us the qryGroupbyWeek?

 

[Angel69]

Yes, here it is:

SELECT DatePart("m",[Approved Date]) AS [Month], DatePart("ww",[Approved Date]) AS Week, tblPACSTemplate.[Approved Date], tblPACSTemplate.ID
FROM tblPACSTemplate
GROUP BY DatePart("m",[Approved Date]), DatePart("ww",[Approved Date]), tblPACSTemplate.[Approved Date], tblPACSTemplate.ID;