Sum Query based on date values (1 Viewer)

[khurram7x]

Hi,
I want to Sum values from qryWeeklySE_WorkPackEmployee_GroupBy where Activity Date in this query lies between Start and End Date from query qryWeekly_Verified_Report.


This is too complex for me. I've tried full day trying lot of different stuff but nothing has worked for me yet!


Attaching screenshot. Help please!


Thanks,
K

 

[Uncle Gizmo]

The image isn't telling me anything useful...

 

[khurram7x]

The image isn't telling me anything useful...

What else required please? I just need Sum of value in table on left, i.e. Working, Lost and Rest Hours, in Groups, where Activity Date lies between Start and End Dates in table on right... and then I'll combine certain other fields from both tables based on required report format.

 

[Minty]

It might help if you posted up the sql of the query that isn't working?
Have you tried taking your initial grouped query and creating another one with it and your weekly verified qry and joining them ?

 

[khurram7x]

It might help if you posted up the sql of the query that isn't working?
Have you tried taking your initial grouped query and creating another one with it and your weekly verified qry and joining them ?

The query I'm trying to solve is based on two other queries. 'qryWeekly_Verified_Report' is a simple combination of several tables using JOINs. Second query, i.e. 'qryWeeklySE_WorkpackEmployee_GroupBy' is a group query, attaching screenshot for your review, shows the actual GUI query used to achieve this.

Finally, below is the query I'm trying, to achieve similar results for what I've requested help in Post 1, though unfruitful. I don't know how to deal with dates properly in my case, ended up asking help from you great guys.

SELECT qryrWeekly_Verified_Report.Start_Date, qryrWeekly_Verified_Report.End_Date, Sum(qryWeeklySE_WorkpackEmployee_GroupBy.WorkingHours) AS SumOfWorkingHours, Sum(qryWeeklySE_WorkpackEmployee_GroupBy.LostHours) AS SumOfLostHours, Sum(qryWeeklySE_WorkpackEmployee_GroupBy.RestHours) AS SumOfRestHours
FROM qryWeeklySE_WorkpackEmployee_GroupBy INNER JOIN qryrWeekly_Verified_Report ON (qryWeeklySE_WorkpackEmployee_GroupBy.WorkPackID = qryrWeekly_Verified_Report.WorkPack) AND (qryWeeklySE_WorkpackEmployee_GroupBy.AreaID = qryrWeekly_Verified_Report.Area) AND (qryWeeklySE_WorkpackEmployee_GroupBy.SupervisorID = qryrWeekly_Verified_Report.SupervisorID)
GROUP BY qryWeeklySE_WorkpackEmployee_GroupBy.ActivityDate, qryrWeekly_Verified_Report.Start_Date, qryrWeekly_Verified_Report.End_Date
HAVING (((qryrWeekly_Verified_Report.Start_Date)>=[qryWeeklySE_WorkpackEmployee_GroupBy].[ActivityDate]) AND ((qryrWeekly_Verified_Report.End_Date)<=[qryWeeklySE_WorkpackEmployee_GroupBy].[ActivityDate]));

Attaching GUI of query as well.

Thank you,
K

 

[JHB]

Does it not run or what is the problem?

 

[Minty]

Remove the grouping and sums on the final query, you already are doing that on the underlying query.

 

[khurram7x]

Thank you so much all, this query solved my problem.

Regards,
K

SELECT qryWeeklySE_WorkpackEmployee_GroupBy.SupervisorID, qryrWeekly_Verified_Report.Area, qryrWeekly_Verified_Report.WorkPack, Sum(qryWeeklySE_WorkpackEmployee_GroupBy.WorkingHours) AS SumOfWorkingHours, Sum(qryWeeklySE_WorkpackEmployee_GroupBy.LostHours) AS SumOfLostHours, Sum(qryWeeklySE_WorkpackEmployee_GroupBy.RestHours) AS SumOfRestHours, qryrWeekly_Verified_Report.Start_Date, qryrWeekly_Verified_Report.End_Date
FROM qryWeeklySE_WorkpackEmployee_GroupBy INNER JOIN qryrWeekly_Verified_Report ON (qryWeeklySE_WorkpackEmployee_GroupBy.WorkPack = qryrWeekly_Verified_Report.WorkPack) AND (qryWeeklySE_WorkpackEmployee_GroupBy.Area = qryrWeekly_Verified_Report.Area) AND (qryWeeklySE_WorkpackEmployee_GroupBy.SupervisorID = qryrWeekly_Verified_Report.SupervisorID)
WHERE (((qryWeeklySE_WorkpackEmployee_GroupBy.ActivityDate) Between [Start_Date] And [End_Date]))
GROUP BY qryWeeklySE_WorkpackEmployee_GroupBy.SupervisorID, qryrWeekly_Verified_Report.Area, qryrWeekly_Verified_Report.WorkPack, qryrWeekly_Verified_Report.Start_Date, qryrWeekly_Verified_Report.End_Date;