Create new rows of data based on range of dates in another table-CodePudding

I have a table that contains a start date, an end date, and number of hours. I would like to create rows of data in a new table based on these values.

ID	StartDate	EndDate	Hours
1	3/2022	6/2022	40
2	4/2022	4/2023	144

What I would like do is create a table that evenly splits the hours over the date range.

ForeignKey	MonthYear	Hours
1	3/2022	10
1	4/2022	10
1	5/2022	10
1	6/2022	10
2	4/2022	12
2	4/2022	12
2	5/2022	12
2	6/2022	12
2	7/2022	12
2	8/2022	12
2	9/2022	12
2	10/2022	12
2	11/2022	12
2	12/2022	12
2	1/2023	12
2	2/2023	12
2	3/2023	12
2	4/2023	12

I have seen examples on how to do this using CTEs but they are not available in MS Access. I am trying to find a way to do this without VBA if possible. If necessary I will resort to VBA.

CodePudding user response：

Here is VBA to get you started. It uses table with date/time type fields holding a full date value:

Sub ExpandData()
Dim db As DAO.Database, rs As DAO.Recordset
Dim dteStartDate As Date, dteEndDate As Date, x As Integer, intMonths As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT ID, StartDate, EndDate, DateDiff('m',[StartDate],[EndDate]) 1 AS Months, Hours FROM Data;")
db.Execute "DELETE FROM DataExpanded"
Do While Not rs.EOF
    For x = 0 To rs!Months - 1
        db.Execute "INSERT INTO DataExpanded(ForeignKey, MonthDayYear, Hours) " & _
                    "VALUES(" & rs!ID & "," & DateAdd("m", x, rs!startDate) & "," & rs!Hours / rs!Months & ")"
    Next
    rs.MoveNext
Loop
End Sub

CodePudding user response：

You can do it with SQL using a Cartesian (multiplying) query (where Hours is your source table):

SELECT DISTINCT 
    Hours.ID, 
    DateAdd("m",10*Abs([Deca].[id] Mod 10) Abs([Uno].[id] Mod 10),[StartDate]) AS MonthYear, 
    [Hours]/(1 DateDiff("m",[StartDate],[EndDate])) AS HoursPerMonth
FROM 
    MSysObjects AS Uno, 
    MSysObjects AS Deca, 
    Hours
WHERE 
    DateAdd("m",10*Abs([Deca].[id] Mod 10) Abs([Uno].[id] Mod 10),[StartDate])<=[EndDate];

The output will be different from your expected output, as you for ID 2 think you have 12 months, but do have 13: