Problem With Query

[D Willett]

Hi Guys
I have a problem with a query. I'm grouping by year and need to divide [Tot Labour] by [Tot Hrs] but the result produced is incorrect. Can anyone see an error in the below SQL?

SELECT [qryARC-WorkProvider-Dates].Type, Sum([qryARC-WorkProvider-Dates].[Tot Hrs]) AS [SumOfTot Hrs], Sum([qryARC-WorkProvider-Dates].[Tot Labour]) AS [SumOfTot Labour], Format([InvoicedDate],"yyyy") AS [Year], Sum(IIf([Tot Hrs]=0,0,[Tot Labour]/[Tot Hrs])) AS AvgRate
FROM [qryARC-WorkProvider-Dates]
GROUP BY [qryARC-WorkProvider-Dates].Type, Format([InvoicedDate],"yyyy");

ScreenHunter_054.jpg

Many Thanks

[HansV]

How about

[SumOfTot Labour]/[SumOf Tot Hrs] AS AvgRate

or

Sum([qryARC-WorkProvider-Dates].[Tot Labour])/Sum([qryARC-WorkProvider-Dates].[Tot Hrs]) AS AvgRate

[D Willett]

Hi Hans
Each row calculates correctly if I take out the Totals Grouping row? And I have Div zero issues too. It's only when the Totals function from the ribbon menu is selected.

[HansV]

Yeah, but the overall average is not equal to the sum of the individual averages.
How about

IIf([SumOf Tot Hrs]=0,Null,[SumOfTot Labour]/[SumOf Tot Hrs]) AS AvgRate

Set the Total option to Expression (I forgot to mention that in my previous reply)

[D Willett]

This works:

SELECT [qryARC-WorkProvider-Dates].Type, Sum([qryARC-WorkProvider-Dates].[Tot Labour]) AS Lab, Sum([qryARC-WorkProvider-Dates].[Tot Hrs]) AS Hrs, Format([InvoicedDate],"yyyy") AS InvYear, IIf([Hrs]=0,0,[Lab]/[Hrs]) AS AvgRate
FROM [qryARC-WorkProvider-Dates]
GROUP BY [qryARC-WorkProvider-Dates].Type, Format([InvoicedDate],"yyyy");

Thanks for the direction Hans.