# 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
###### Who is Participating?

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.

Commented:
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.
Author Commented:
The week will be mapped to whatever month the week starts in
Author Commented:
As a start, I've created a weekly mapping table that maps the week numbers to the month numbers.
CIOCommented:
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.

' Date of requested weekday in requested week of year intYear.
datDateOfFirstWeek = DateAdd("ww", bytWeek - 1, datDateOfFirstWeek)
End If

ISO_DateOfWeek = datDateOfFirstWeek

End Function
``````
/gustav
Author 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?
Author Commented:
In the client's environment, the first week of 2015 starts on 12/29/2014.
CIOCommented:
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
Author Commented:
You're right.  I was given the wrong information.  The client's week does start on a Monday and not on sunday.
Commented:
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
dCurMonth = dFirstMonth
Do
Set fNewField = tblMonthly.CreateField("Month " & Month(dCurMonth) & "/" & Year(dCurMonth), dbLong)
tblMonthly.Fields.Append fNewField
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
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
``````

Experts Exchange Solution brought to you by