In Microsoft Access, I have a table where number of hours worked per weekday are associated to a project number, ISO-week number and year. Simplified, it looks like this:

I am trying to set up a query where all weekday columns (monday to sunday) are "merged" into one column and the week and year numbers are converted to dates. It should look like this:

I managed to merge all weekday columns into one column using a UNION query:

SELECT ProjectID, WeekNumber, YearNumber, Monday As HoursPerDay FROM ProjectHours
UNION ALL
SELECT ProjectID, WeekNumber, YearNumber, Tuesday As HoursPerDay FROM ProjectHours;

The result looks like this:

But I am stuck converting the iso-week and year number to a date. Is this at all possible in Access-SQL?

I found this question on Stackoverflow and went through the date/time functions that Microsoft lists for Access but could not get it to work.

Any help is highly appreciated.

EDIT: A community member has suggested a possible duplicate of this question, but I am looking for a solution that I can use in an Access-SQL query, so the suggested duplicate does not help me, as it suggests a VBA based solution.

CodePudding user response：

This is not so simple, as the ISO years rarely are in sync with calendar years.

But this function will do:

' Returns the date of Monday for the ISO 8601 week of IsoYear and Week.
' Optionally, returns the date of any other weekday of that week.
'
' 2017-05-03. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateYearWeek( _
    ByVal IsoWeek As Integer, _
    Optional ByVal IsoYear As Integer, _
    Optional ByVal DayOfWeek As VbDayOfWeek = VbDayOfWeek.vbMonday) _
    As Date
    
    Dim WeekDate    As Date
    Dim ResultDate  As Date
    
    If IsoYear = 0 Then
        IsoYear = Year(Date)
    End If
    
    ' Validate parameters.
    If Not IsWeekday(DayOfWeek) Then
        ' Don't accept invalid values for DayOfWeek.
        Err.Raise DtError.dtInvalidProcedureCallOrArgument
        Exit Function
    End If
    If Not IsWeek(IsoWeek, IsoYear) Then
        ' A valid week number must be passed.
        Err.Raise DtError.dtInvalidProcedureCallOrArgument
        Exit Function
    End If
    
    WeekDate = DateAdd(IntervalSetting(dtWeek), IsoWeek - 1, DateFirstWeekYear(IsoYear))
    ResultDate = DateThisWeekPrimo(WeekDate, DayOfWeek)
    
    DateYearWeek = ResultDate

End Function

However, it uses a series of supporting functions, like:

' Returns the primo date of the week of the date passed.
'
' 2016-01-13. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateThisWeekPrimo( _
    ByVal DateThisWeek As Date, _
    Optional ByVal FirstDayOfWeek As VbDayOfWeek = vbSunday) _
    As Date

    Dim Interval    As String
    Dim Number      As Double
    Dim ResultDate  As Date
    
    Number = 0
    Interval = IntervalSetting(DtInterval.dtWeek)
    
    ResultDate = DateIntervalPrimo(Interval, Number, DateThisWeek, FirstDayOfWeek)
    
    DateThisWeekPrimo = ResultDate
    
End Function

and several more - way too much to post here.

So, please refer to my project at GitHub, VBA.Date, for the modules holding the full code.