MS Access Weekly query

Hiroyuki Tamura
Hiroyuki Tamura used Ask the Experts™
on
I'm trying to create a weekly query. from Mon to Sun.
I tried format ([time],"ww") but can't understand how it works.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
I'm not sure where you are going with this so I just made a sample from one of my tables.

SELECT Year([LastChangeDT]) AS ChngYear, Format([LastChangeDT],"ww") AS ChngWeek, Count(*) AS Expr1
FROM tblChangeDT
GROUP BY Year([LastChangeDT]), Format([LastChangeDT],"ww");

This query returns:
CountByWeek.JPG
Hiroyuki TamuraField Engineer

Author

Commented:
Thank you. How does "ww" define a week?
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Access will not return correct ISO weeknumbering and weeks cross calendar years, so you will need a custom function like this:

Public Function ISO_WeekYearNumber( _
  ByVal datDate As Date, _
  Optional ByRef intYear As Integer, _
  Optional ByRef bytWeek As Byte) _
  As String

' Calculates and returns year and week number for date datDate according to the ISO 8601:1988 standard.
' Optionally returns numeric year and week.
' 1998-2007, Gustav Brock, Cactus Data ApS, CPH.
' May be freely used and distributed.

  Const cbytFirstWeekOfAnyYear  As Byte = 1
  Const cbytLastWeekOfLeapYear  As Byte = 53
  Const cbytMonthJanuary        As Byte = 1
  Const cbytMonthDecember       As Byte = 12
  Const cstrSeparatorYearWeek   As String = "W"
  
  Dim bytMonth                  As Byte
  Dim bytISOThursday            As Byte
  Dim datLastDayOfYear          As Date

  intYear = Year(datDate)
  bytMonth = Month(datDate)
  bytWeek = DatePart("ww", datDate, vbMonday, vbFirstFourDays)
  
  If bytWeek = cbytLastWeekOfLeapYear Then
    bytISOThursday = Weekday(vbThursday, vbMonday)
    datLastDayOfYear = DateSerial(intYear, cbytMonthDecember, 31)
    If Weekday(datLastDayOfYear, vbMonday) >= bytISOThursday Then
      ' OK, week count of 53 is caused by leap year.
    Else
      ' Correct for Access97/2000+ bug.
      bytWeek = cbytFirstWeekOfAnyYear
    End If
  End If

  ' Adjust year where week number belongs to next or previous year.
  If bytMonth = cbytMonthJanuary Then
    If bytWeek >= cbytLastWeekOfLeapYear - 1 Then
      ' This is an early date of January belonging to the last week of the previous year.
      intYear = intYear - 1
    End If
  ElseIf bytMonth = cbytMonthDecember Then
    If bytWeek = cbytFirstWeekOfAnyYear Then
      ' This is a late date of December belonging to the first week of the next year.
      intYear = intYear + 1
    End If
  End If
  
  ISO_WeekYearNumber = CStr(intYear) & cstrSeparatorYearWeek & Format(bytWeek, "00")

End Function

Open in new window

Then, in your query, group by week-year:

Select Sum([SomeField])
From YourTable
Group By  ISO_WeekYearNumber([YourDateField])

Open in new window

/gustav
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017
Commented:
You should look up the syntax for Format() and that will tell you.  Or, you can start typing in a VBA code module or in the immediate window and Intellisense will help you.  the third argument below specifies which weekday is the first day of a week.  The fourth argument specifies how you want to handle the first week of the year.  I chose the Jan1 option which says that regardless of what day of the week Jan1 falls on, call that week 1.  So if Jan1 is a Sunday, week 1 will be just that date.

Format(Date(),"ww",vbMonday,vbFirstJan1)  '''That configuration makes today Week 14
Format(Date(),"ww",vbMonday,vbFirstFourDays)   ''That configuration makes today Week 13
Distinguished Expert 2017

Commented:
Gus' function returns 2017W13 for today's date.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Problem is, that Format and DatePart don't always get week 53 right. Also, both week 1 and 52/53 can belong to either a calendar year before or after New Year. Thus, you need that year part as well to sort and group correctly when data spans calendar years.

/gustav
Distinguished Expert 2017

Commented:
That's why I included Year in my query example.  Since year does not divide cleanly into weeks, it is necessary to decide how to handle the first week of the year when it doesn't start on the first day of the week and that also impacts the definition of the Last week of the year.  The ISO method may be what Tamura wants but in this case, the correct answer is actually what satisfies the business users.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Who knows? Questioneer is  silent.

/gustav
Hiroyuki TamuraField Engineer

Author

Commented:
Thank you, all. I'm trying to add ISO method.
is this the right way?
Snap1972.png
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes.
Hiroyuki TamuraField Engineer

Author

Commented:
Thank you all

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial