Sort Report by Sum Value

[traycee]

I'm stumped. This is probably something really simple that I am overlooking. I have a mileage report. This report lists the workers name. Under their name it shows their detailed mileage and then sums up the total mileage for each individual worker.

John Doe

29
36
23
___
88

What I'm trying to do, is to get the report to sort by the workers mileage 'sum'. So that the report lists the workers from highest total mileage first in the report down to the last worker with the least amount of miles.

Thanks for any thoughts you might have on this subject.

 

[jebry]

Hi!

Do the sum in a query like this:

query1

Select EmployeeId, Sum(Mileage) As TotalMiles From YourTable Group By EmployeeId

Then create a query with all of the information you need and include this sum:

query2

Select YourTable.EmployeeId, Mileage, TotalMiles From YourTable InnerJoin query1 On YourTable.EmployeeId = query1.EmployeeId

Of course you would also include other information (like name) as needed for the report. Now base your report on query2 and you will be able to sort by TotalMiles.

hth


Jeff Bridgham
bridgham@purdue.edu