'Convert week number and year to a date in Access-SQL?
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:
| ProjectID | WeekNumber | YearNumber | Monday | Tuesday |
|---|---|---|---|---|
| 1 | 1 | 2022 | 5 | 6 |
| 1 | 2 | 2022 | 7 | 8 |
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:
| ProjectID | Date | HoursPerDay |
|---|---|---|
| 1 | 03.01.2022 | 5 |
| 1 | 04.01.2022 | 6 |
| 1 | 10.01.2022 | 7 |
| 1 | 11.01.2022 | 8 |
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:
| ProjectID | WeekNumber | YearNumber | HoursPerDay |
|---|---|---|---|
| 1 | 1 | 2022 | 5 |
| 1 | 1 | 2022 | 6 |
| 1 | 2 | 2022 | 7 |
| 1 | 2 | 2022 | 8 |
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
