Sort Desc

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Sort Desc

Post by D Willett »

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));
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sort Desc

Post by HansV »

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)
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Sort Desc

Post by D Willett »

Hi Hans

It asks me for Parameter values on TG and TT ??
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sort Desc

Post by HansV »

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;
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Sort Desc

Post by D Willett »

Works for me Hans :thumbup:

Thanks
Cheers ...

Dave.