I have 2 tables. One has a single column that contains week starting dates for 18 months. The other table has a customer, start date, end date and hours worked fields. I want to create a table that shows by week starting in table 1 the customer and number of hours worked each week divided by the number of weeks specified by the date range in table 2. The dates used in table two match those in table 1.
Table 1
Week Starting
4/7/14
4/14/14
4/21/14
4/28/14
5/5/14
5/12/14
Table 2
Customer Start End Hours Worked
Acme 4/14/14 4/21/14 100
Supply 4/21/14 5/5/14 150
New Table or Query Result
Week Hours Worked
4/7/14
4/14/14 50
4/21/14 100
4/28/14 50
5/5/14 50
5/12/14
Not sure how to achieve this. Thanks in advance.