Group By - Sum with Date Range

[Airbisk]

Hi,

I have a table that contain jobdate, staffname, overtimehrs, standardhrs field. Now I can sum the number of overtimehrs and standardhrs using Group by and Sum but when I add the criteria for a date range to be selected (Between [Start date] and {End Date]) the query returns multi lines for each staffname everytime the jobdate is different.

Is there a way of selecting a date range with out it affecting the query view and still show a total for each staffname only.

Many Thanks

 

[Golom]

Can't be sure without seeing your SQL but you likely have jobdate in the Select and the Group By.

Remove it from both places and make sure that your BETWEEN constraint is in a WHERE clause ... not a HAVING clause.

 

[Airbisk]

Thanks Golom, SQL code below

SELECT [Employee Info].[Employee Name], Sum([Full Job Data].[Standard Hours]) AS [SumOfStandard Hours], Sum([Full Job Data].[Overtime Hours]) AS [SumOfOvertime Hours]

FROM [Employee Info] INNER JOIN [Full Job Data] ON [Employee Info].StaffNo = [Full Job Data].StaffNo

GROUP BY [Employee Info].[Employee Name]

HAVING ((([Full Job Data].[Job Date]) Between [Start Date] And [End Date]));


************************

I have tried changing HAVING to WHERE but its not accepted, Jobdate did appear in the GROUP BY statement and have taken that out.

Any further help appreciated.

Regards

 

[Golom]

A WHERE clause must precede the GROUP BY. Just changing HAVING to WHERE and leaving it in the same place will raise an error.

Try this

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT E.[Employee Name], 
       Sum(F.[Standard Hours]) AS [SumOfStandard Hours], 
       Sum(F.[Overtime Hours]) AS [SumOfOvertime Hours]

FROM [Employee Info] As E INNER JOIN [Full Job Data] As F 
     ON E.StaffNo = F.StaffNo

WHERE F.[Job Date] Between [Start Date] And [End Date]

GROUP BY E.[Employee Name]

I've included a PARAMETERS statement because there are times when a user supplied parameter that should be interpreted as a date gets interpreted as text or numeric.

 

[Airbisk]

Fantastic!!!

That works , thanks Golom

 

[dhookom]

Airbisk,
It's appropriate to award a star to someone who provides "Fantastic" answers. Click the link to "Thank Golom for this valuable post!"

Duane
Hook'D on Access
MS Access MVP