Ordering a recordset

[emozley]

Hi,

I have a query that totals up the number of days sick people have had. The total that is calculated is stored as 'SicknessTotal'.

Is it possible to change the query so that it puts them min order of sickness total? If I add "ORDER BY FirstName ASC" or "ORDER BY Department ASC" it works but as SicknessDate isn't a field I can't.

SELECT Users.UserID, Users.FirstName, Users.Surname, Departments.Department, (SELECT SUM(SicknessDays) FROM StaffSickness WHERE DatePart('yyyy',SicknessDate)=2007 AND StaffSickness.UserID=Users.UserID) AS SicknessTotal
FROM (Users LEFT JOIN StaffSickness ON Users.UserID=StaffSickness.UserID) LEFT JOIN Departments ON Users.Department=Departments.DepartmentID
GROUP BY Users.UserID, Users.FirstName, Users.Surname, Departments.Department;

 

[PaulBricker]

Try
ORDER BY SUM(Sickness Days)

Paul

 

[emozley]

Perfect!

cheers