I have a schedule of rooms which are used at different times of the week (acticvity) (for a total duration). The information i have has a start week and an end week. Each activity might only go for 4 weeks in a year.

 

The end result to to establish how many hours each room is used each week. (ultimately in a chart).

 

I related the start and end weeks to a new table (52 records for the weeks). That worked but didn't help my weekly totals.

IIUC, you may need a join table with roomID, activityID, the date/week information (from which you can build a range), and the hours. Then you can find the respective roomID and week combinations, starting either from rooms or weeks, and sum up the hours.