( Feels strange only me posting problems !! )
I have a query returning efficiency.
The calculation is Time Given divided by Time Taken which should return efficiency.
With the following query, I'm getting the wrong results in the TotEff column.
Working down the columns, the efficiency should be:
1.315....etc
0.977....
1.15 ....
etc
SELECT qryLabourTransaction.Name, Sum(qryLabourTransaction.ALL_HRS) AS SumOfALL_HRS, Sum(qryLabourTransaction.TAK_HRS) AS SumOfTAK_HRS, Sum([ALL_HRS]/[TAK_HRS]) AS TotEff
FROM qryLabourTransaction
GROUP BY qryLabourTransaction.Name;
Calculate Efficiency
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Calculate Efficiency
You do not have the required permissions to view the files attached to this post.
Cheers ...
Dave.
Dave.
-
- 4StarLounger
- Posts: 482
- Joined: 24 Jan 2010, 15:02
- Location: Colorado, USA
Re: Calculate Efficiency
Your expression Sum([ALL_HRS]/[TAK_HRS]) AS TotEff
should be
SumOfAll_HR/sumOfTAK_HR as TotEff
You may or may not be able to do that in the same query - make sure it is at the end and set the Group By to Expression. If that doesn't work, create a select query based on the Group By query and put the Expression there.
should be
SumOfAll_HR/sumOfTAK_HR as TotEff
You may or may not be able to do that in the same query - make sure it is at the end and set the Group By to Expression. If that doesn't work, create a select query based on the Group By query and put the Expression there.
Wendell
You can't see the view if you don't climb the mountain!
You can't see the view if you don't climb the mountain!
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Calculate Efficiency
Alternatively, change
Sum([ALL_HRS]/[TAK_HRS]) AS TotEff
to
Sum([ALL_HRS])/Sum([TAK_HRS]) AS TotEff
Sum([ALL_HRS]/[TAK_HRS]) AS TotEff
to
Sum([ALL_HRS])/Sum([TAK_HRS]) AS TotEff
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England