Nested SQL queery ? for timesheet

I have 3 tables:-

(Tasks - list of all the tasks to complete)
Tasks.IMS, = ID of task i.e. 1 (unique)
Tasks.Title, = Title of task i.e. "Build Wall"


(Activities - list of users for each task, i.e. entry for "brick layer, = 24 hours" and entry for "foreman, = 8 hours")
Activities.ID = unique ID i.e. 100
Activities.IMS = links to the Task IMS - i.e. =1
Activities.[Remaining Effort]) = 24 hours

(Timesheet - one entry per activity (i.e. one for brick layer and a second for foreman) per week)
TimeSheet.Activity = links to Activity.ID
TimeSheet.SAT = number of hours booked for Saturday
TimeSheet.SUN
TimeSheet.MON
TimeSheet.TUE
TimeSheet.THU
TimeSheet.WED
TimeSheet.FRI

I simply need a query that will list, each task, the total remaining effort and the total number of hours booked.

My problem is that with as there are multiple time sheets per activity, when I sum the "Remaining Effort", I get a number that is to large.

I.e.

Task = build Wall
Activity = Brick Layer - 24 hoours remaining
Activity = Foreman - 8 hours remainin
TimeSheet.Brick Layer.Week 1 = 30 hours
TimeSheet.Brick Layer.Week 2 = 32 hours
TimeSheet.Foreman.Week 1 = 8 hours
TimeSheet.Foreman.Week 2 = 7 hours

I expect to get
Worked = 30+32+8+7 = 77
SumOfRemaining Effort = 24 +8 = 32

However I get
Worked = 30+32+8+7 = 77 - OK
SumOfRemaining Effort = 24 +8 +24+8= 64 - WRONG

I think I need some type of nested queery ??????? - please help


This is the Queery I have used.

SELECT
Tasks.IMS,
Tasks.Title,
Tasks.[Budget (Hours)],
Sum(Activities.[Remaining Effort]) AS [SumOfRemaining Effort],
Sum(TimeSheet.SAT) AS SumOfSAT,
Sum(TimeSheet.SUN) AS SumOfSUN,
Sum(TimeSheet.MON) AS SumOfMON,
Sum(TimeSheet.TUE) AS SumOfTUE,
Sum(TimeSheet.THU) AS SumOfTHU,
Sum(TimeSheet.WED) AS SumOfWED,
Sum(TimeSheet.FRI) AS SumOfFRI,
Sum([SAT]+[SUN]+[MON]+[TUE]+[WED]+[THU]+[FRI]) AS Worked
FROM (Tasks LEFT JOIN Activities ON Tasks.IMS = Activities.IMS) LEFT JOIN TimeSheet ON Activities.ID = TimeSheet.Activity GROUP BY Tasks.IMS, Tasks.Title;