Spread hours between start and end dates

[nigelbloomy]

I have a table which shows me total hours for a part and a start date and an end date. I would like to get a table with a new line for each month and the hours spread between the start and end dates. I looked at crosstab queries, but that puts all of the months across the top and I couldn't get it to spread the hours. I created a query that shows hours per day by taking the total hours and dividing it into the end date minus the start date. I also created a table that has every day on it with a calculated column that shows what month that day is in. None of that seems to be helping though.

Right now I have Part A, 10 hours, start 1/31/2015, end 2/1/2015.
What I would like is
Part A, 5 hours, 1/1/2015


Part A, 5 hours, 2/1/2015

How do I do that?

[nigelbloomy]

This is how I got it to work. Query1 gets the part number, hours, start and end dates. Query2 creates an hours per day value (total hours/(end date-start date)). Query3 uses the calendar table I made to create a line for each day between the start and end dates. It shows me the hours per day value and the Date as a month value (1/1/2015 for any day in January) for each line. Query4 takes query3 and sums the per day values by the month date. It seems like a lot of work, but it works to spread the hours showing a new line for each month.

Query3

SELECT [Resources for tasks].TASK_CODE, Table1.DateFull, [Hours per day].PerDay, Table1.DateMonth
FROM Table1, [Resources for tasks] INNER JOIN [Hours per day] ON ([Resources for tasks].TASK_CODE = [Hours per day].TASK_CODE)
WHERE (((Table1.DateFull) Between [Resources for tasks]![START_DATE] And [Resources for tasks]![END_DATE]));

Query4

SELECT [Totals By Day].TASK_CODE, [Totals By Day].DateMonth, Sum([Totals By Day].PerDay) AS SumOfPerDay
FROM [Totals By Day]
GROUP BY [Totals By Day].TASK_CODE, [Totals By Day].DateMonth;