Hi guys
With the following query, how can I set the order desc for the field [Eff]
SELECT [Surname] & " " & [Forename] AS Employee, Sum(tblEff.TotalAllocated) AS TG, Sum(tblEff.TotalBooked) AS TT, Sum(tblEff.Saved) AS [Saved Hours], IIf([TT]=0,0,[TG]/[TT])*100 AS Eff
FROM tblEff
GROUP BY [Surname] & " " & [Forename]
HAVING (((Sum(tblEff.TotalAllocated))>16));
Sort Desc
-
- Administrator
- Posts: 78378
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sort Desc
Try
SELECT [Surname] & " " & [Forename] AS Employee, Sum(tblEff.TotalAllocated) AS TG, Sum(tblEff.TotalBooked) AS TT, Sum(tblEff.Saved) AS [Saved Hours], IIf([TT]=0,0,[TG]/[TT])*100 AS Eff
FROM tblEff
GROUP BY [Surname] & " " & [Forename]
HAVING (((Sum(tblEff.TotalAllocated))>16))
ORDER BY IIf([TT]=0,0,[TG]/[TT])*100 DESC;
(I haven't tested it)
SELECT [Surname] & " " & [Forename] AS Employee, Sum(tblEff.TotalAllocated) AS TG, Sum(tblEff.TotalBooked) AS TT, Sum(tblEff.Saved) AS [Saved Hours], IIf([TT]=0,0,[TG]/[TT])*100 AS Eff
FROM tblEff
GROUP BY [Surname] & " " & [Forename]
HAVING (((Sum(tblEff.TotalAllocated))>16))
ORDER BY IIf([TT]=0,0,[TG]/[TT])*100 DESC;
(I haven't tested it)
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
-
- Administrator
- Posts: 78378
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sort Desc
OK, then try this:
SELECT Temp.*
FROM (SELECT [Surname] & " " & [Forename] AS Employee, Sum(tblEff.TotalAllocated) AS TG, Sum(tblEff.TotalBooked) AS TT, Sum(tblEff.Saved) AS [Saved Hours], IIf([TT]=0,0,[TG]/[TT])*100 AS Eff
FROM tblEff
GROUP BY [Surname] & " " & [Forename]
HAVING (((Sum(tblEff.TotalAllocated))>16))) AS Temp
ORDER BY Temp.Eff DESC;
SELECT Temp.*
FROM (SELECT [Surname] & " " & [Forename] AS Employee, Sum(tblEff.TotalAllocated) AS TG, Sum(tblEff.TotalBooked) AS TT, Sum(tblEff.Saved) AS [Saved Hours], IIf([TT]=0,0,[TG]/[TT])*100 AS Eff
FROM tblEff
GROUP BY [Surname] & " " & [Forename]
HAVING (((Sum(tblEff.TotalAllocated))>16))) AS Temp
ORDER BY Temp.Eff DESC;
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England