Weekly or monthly reports on Access

[duddu]

Sent from my iPhone using Tapatalk

[June7]

Can easily calculate Year and Month from date value. Use that calculated value to group and summarize data.

Pulling the week can be a bit more complicated if you require a week to be a full 7-day period because some 7-day periods cross months and years.

Format(datevalue,"ww") will pull week number for the calendar year but the first and last weeks will not always be 7 days.

[ArviLaanemets]

I have a formula to calculate ISO weeknumber (the week belongs to year, where it's Thursday belongs) in Excel, and I ported it also into TSQL as function returning week number in format yyyyww, but I can't access either currently.

Anyway, when you need summarize over weeks, the calculation will be too complex to use it directly in query (the query will be too slow). The better solution will be to add a tblCalendary table (CalDate, WeekNo), fill it with dates and week numbers p.e. fo 10 years, and use it in queries. And you can add an OnOpen event which adds rows to tblCalendary when max CalDate is less than some predefined number of days from today.