How to convert dynamic week numbers to months in query

Hello,

I have a stock table that contains week number, in this case the weeks run from week 18 to week 41.  I am trying to write a query that will combine week into months.  A complication, is that the starting week numbers increment every week so that next week, the week number swill be week 19 to week 42.  Basically Week 18 will drop off and week 42 will be added to the table. I want to be able to aggregate the weekly values into monthly values in a environment where starting and ending weeks increment from week to week.  I've exported the table to an excel sheet and have attached the sheet.
PipelineFinalTable.xlsx
Juan VelasquezAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BeartlaoiCommented:
Understanding that weeks can span two different months.
You have to decide how you are going to map a week to a month on the edges of the months.
I see three options.
1. map the week to whatever month has more days in that week
2. map the week to whatever month the week starts in
3. map the week to whatever month the week ends in
Once you decide which way to go, then we can figure out equations to do the work.

Although if you can get daily data from your source, then the problem becomes very simple and more accurate.
Juan VelasquezAuthor Commented:
The week will be mapped to whatever month the week starts in
Juan VelasquezAuthor Commented:
As a start, I've created a weekly mapping table that maps the week numbers to the month numbers.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Gustav BrockCIOCommented:
This is not as easy as you may think, as weeks also cross year boundaries.

But - using the function below - you can first find the first date of the (year and) week, then the year-month of that date with this expression:

YearMonth: Format(ISO_DateOfWeek(Year(Date()), [YourWeekNumber]), "yyyy-mm")

For week 1 this will return 2014-12, for week 2 it is 2015-01, and so on.
Public Function ISO_DateOfWeek( _
  ByVal intYear As Integer, _
  ByVal bytWeek As Byte, _
  Optional ByVal bytWeekday As Byte = vbMonday) _
  As Date

' Calculates date of requested weekday in a week of
' a year according to ISO 8601:1988 standard.
'
' Notes:  Years less than 100 will be handled as
'         two-digit years of our current year frame.
'         Years less than zero returns a zero date.
'         A weeknumber of zero returns the requested
'         weekday of the week before week 1.
'
' 2000-12-17. Cactus Data ApS, Gustav Brock.

  ' The fourth of January is always included in
  ' the first week of year intYear.
  Const cbytDayOfFirstWeek  As Byte = 4
  ' Number of days in a week.
  Const cbytDaysOfWeek      As Byte = 7
  ' Month of January.
  Const cbytJanuary         As Byte = 1
  
  Dim datDateOfFirstWeek    As Date
  Dim intISOMonday          As Integer
  Dim intISOWeekday         As Integer
  Dim intWeekdayOffset      As Integer
  
  ' No specific error handling.
  On Error Resume Next
    
  If intYear > 0 Then
    ' Weekday of Monday.
    intISOMonday = Weekday(vbMonday, vbMonday)
    ' Date of fourth of January in year intYear.
    datDateOfFirstWeek = DateSerial(intYear, cbytJanuary, cbytDayOfFirstWeek)
    ' Weekday of fourth of January in year intYear.
    intISOWeekday = Weekday(datDateOfFirstWeek, vbMonday)
    ' Calculate offset from Monday in first week of year intYear.
    intWeekdayOffset = intISOMonday - intISOWeekday
    
    ' Weekday of requested weekday.
    intISOWeekday = Weekday(bytWeekday, vbMonday)
    ' Calculate offset from requested weekday in first week of year intYear.
    intWeekdayOffset = intWeekdayOffset + intISOWeekday - intISOMonday
    ' Date of requested weekday in first week of year intYear.
    datDateOfFirstWeek = DateAdd("d", intWeekdayOffset, datDateOfFirstWeek)
  
    ' Date of requested weekday in requested week of year intYear.
    datDateOfFirstWeek = DateAdd("ww", bytWeek - 1, datDateOfFirstWeek)
  End If
  
  ISO_DateOfWeek = datDateOfFirstWeek
  
End Function

Open in new window

/gustav
Juan VelasquezAuthor Commented:
Hello Gustav,
With respect to the btyWeekday, if I pass VbSunday, It would use sunday as the start of the week, is that correct?
Juan VelasquezAuthor Commented:
In the client's environment, the first week of 2015 starts on 12/29/2014.
Gustav BrockCIOCommented:
No, Sunday would be the last date of the week.

If  "the first week of 2015 starts on 12/29/2014", this matches the ISO week numbering, so you should be all set. It's a Monday.

/gustav
Juan VelasquezAuthor Commented:
You're right.  I was given the wrong information.  The client's week does start on a Monday and not on sunday.
BeartlaoiCommented:
Heres a VBA procedure that will create a new table containing the monthly columns instead of the weekly ones.
Option Compare Database
Option Explicit

Function MonthDiff(dStart As Date, dEnd As Date) As Date
    MonthDiff = (Year(dEnd) * 12 + Month(dEnd)) - (Year(dStart) * 12 + Month(dStart))
End Function

Sub CreateMonthlyTable()

    Dim DB As Database, tblWeekly As TableDef, tblMonthly As TableDef, fNewField As Field
    Dim rsWeekly As Recordset, rsMonthly As Recordset
    Dim iField As Integer, iDestField As Integer, nFields As Integer, iWeek As Integer
    Dim iMinWeekCol As Integer, iMaxWeekCol As Integer, iFirstWeek As Integer
    Dim iYear As Integer, iWW As Integer, iWD As Integer, iPrevWeek As Integer
    Dim dJan1 As Date, dLastDOW As Date, iMonth As Integer
    Dim dCurMonth As Date, dFirstMonth As Date, dLastMonth As Date
    Dim ColName As String
    
    Set DB = CurrentDb
    Set tblWeekly = DB.TableDefs("PipelineFinalTable")
    Rem Find the last week column
    nFields = tblWeekly.Fields.Count - 1
    iMaxWeekCol = nFields
    Do
        ColName = tblWeekly.Fields(iMaxWeekCol).Name
        If Left(ColName, 4) = "Week" And IsNumeric(Mid(ColName, 5)) Then Exit Do
        iMaxWeekCol = iMaxWeekCol - 1
    Loop
    Rem Debug.Print tblWeekly.Fields(iMaxWeekCol).Name
    Rem Find the first week column
    iMinWeekCol = iMaxWeekCol - 1
    Do
        ColName = tblWeekly.Fields(iMinWeekCol).Name
        If Left(ColName, 4) <> "Week" Or Not IsNumeric(Mid(ColName, 5)) Then
            iMinWeekCol = iMinWeekCol + 1
            Exit Do
        End If
        iFirstWeek = CInt(Mid(ColName, 5))
        iMinWeekCol = iMinWeekCol - 1
    Loop
    Rem Debug.Print tblWeekly.Fields(iMinWeekCol).Name, iFirstWeek
    ReDim iaWeekCol2MonthCol(tblWeekly.Fields.Count) As Integer
    Rem Match weeks to months
    iYear = Year(Date)
    iWW = CInt(Format(Date, "ww", 2))
    If iWW < iFirstWeek Then iYear = iYear - 1
    dJan1 = DateSerial(iYear, 1, 1)
    iWD = CInt(Format(dJan1, "w", 2)) - 1
    Rem Debug.Print iYear, iWW, iWD
    iPrevWeek = iFirstWeek
    dFirstMonth = DateSerial(iYear + 10, 1, 1)
    ReDim iaCol2Month(iMaxWeekCol - iMinWeekCol + 1) As Date
    For iField = iMinWeekCol To iMaxWeekCol
        ColName = tblWeekly.Fields(iField).Name
        iWeek = Int(Mid(ColName, 5))
        If iWeek < iPrevWeek Then
            iYear = iYear + 1
            dJan1 = DateSerial(iYear, 1, 1)
            iWD = CInt(Format(dJan1, "w", 2)) - 1
        End If
        iPrevWeek = iWeek
        dLastDOW = DateAdd("d", (iWeek - 1) * 7 - iWD + 6, dJan1)
        iMonth = Month(dLastDOW)
        dCurMonth = DateSerial(iYear, iMonth, 1)
        If dCurMonth < dFirstMonth Then dFirstMonth = dCurMonth
        dLastMonth = dCurMonth
        iaWeekCol2MonthCol(iField) = nFields - (iMaxWeekCol - iMinWeekCol) + MonthDiff(dFirstMonth, dCurMonth)
        Rem Debug.Print iWeek, DateAdd("d", (iWeek - 1) * 7 - iWD, dJan1), dLastDOW, iMonth, iField, iaWeekCol2MonthCol(iField)
    Next
    Rem Debug.Print dFirstMonth, dLastMonth
    Rem Create a new Monthly table from the schema of the weekly
    On Error Resume Next
    DB.TableDefs.Delete "PiplelineMonthly"
    On Error GoTo 0
    DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentProject.FullName, acTable, "PipelineFinalTable", "PiplelineMonthly", True
    DB.TableDefs.Refresh
    Set tblMonthly = DB.TableDefs("PiplelineMonthly")
    Rem Remove the weekly columns
    For iField = iMinWeekCol To iMaxWeekCol
        tblMonthly.Fields.Delete tblWeekly.Fields(iField).Name
    Next
    DB.TableDefs.Refresh
    Rem Add Monthly columns
    dCurMonth = dFirstMonth
    Do
        Set fNewField = tblMonthly.CreateField("Month " & Month(dCurMonth) & "/" & Year(dCurMonth), dbLong)
        tblMonthly.Fields.Append fNewField
        dCurMonth = DateAdd("m", 1, dCurMonth)
    Loop Until dCurMonth > dLastMonth
    Rem Copy the rows and add the new data
    Set rsWeekly = tblWeekly.OpenRecordset
    Set rsMonthly = tblMonthly.OpenRecordset
    Do Until rsWeekly.EOF
        rsMonthly.AddNew
        Rem Copy the fields before the weeks
        For iField = 1 To iMinWeekCol - 1
            rsMonthly(iField).Value = rsWeekly(iField).Value
        Next
        Rem Copy the fields after the weeks
        iDestField = iField
        For iField = iMaxWeekCol + 1 To nFields
            rsMonthly(iDestField).Value = rsWeekly(iField).Value
            iDestField = iDestField + 1
        Next
        Rem Fill the Months
        For iField = iMinWeekCol To iMaxWeekCol
            If IsNumeric(rsWeekly(iField).Value) Then
                iDestField = iaWeekCol2MonthCol(iField)
                Rem Debug.Print iField, tblWeekly.Fields(iField).Name, iDestField, tblMonthly.Fields(iDestField).Name
                If IsNull(rsMonthly(iDestField).Value) Then
                    rsMonthly(iDestField).Value = CLng(rsWeekly(iField).Value)
                Else
                    rsMonthly(iDestField).Value = rsMonthly(iDestField).Value + CLng(rsWeekly(iField).Value)
                End If
            End If
        Next
        rsMonthly.Update
        rsWeekly.MoveNext
    Loop
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Juan VelasquezAuthor Commented:
Thanks everyone.  Beartlaoi, the code for creating the Monthly table was exactly what I was looking for. I also found Gustav's ISO_DateOfWeek function very useful.  Thanks again
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.