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");
Many Thanks
Problem With Query
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Problem With Query
You do not have the required permissions to view the files attached to this post.
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Problem With Query
How about
[SumOfTot Labour]/[SumOf Tot Hrs] AS AvgRate
or
Sum([qryARC-WorkProvider-Dates].[Tot Labour])/Sum([qryARC-WorkProvider-Dates].[Tot Hrs]) AS AvgRate
[SumOfTot Labour]/[SumOf Tot Hrs] AS AvgRate
or
Sum([qryARC-WorkProvider-Dates].[Tot Labour])/Sum([qryARC-WorkProvider-Dates].[Tot Hrs]) AS AvgRate
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Problem With Query
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.
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.
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Problem With Query
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)
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)
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Problem With Query
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.
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.
Cheers ...
Dave.
Dave.