Sorting by the sum of a group

[Ruth]

I have a report which lists customers and how much they have spent each month.

I have managed to group the report by the customers so that I have the total
for the year for each customer.

I can sort the report by customer code A-Z, but I really want to sort the
report by Amount Spent That Year so that I have the big spenders at the top
of the list and I just cannot seem to get it to work.

Help! Any solutions would be greatly appreciated - I'm not an expert so
explanations as simple as possible please!

 

[Duane Hookom]

You can't sort a report based on an aggregated value like those generally
found in group headers/footers. You can almost always create a totals query
that calculates this value and then join this totals query in your report's
record source. This will add the aggregated value to the field list for
sorting and grouping.

 

[Ruth]

Thanks for your reply Duane. What do you mean by a Totals Query and how do I
do one?!

 

[Duane Hookom]

Ok, assuming you have a report in the Northwind sample mdb that you want to
show the detail sales for 1996 but sort by Customer Totals. You would first
create the totals query [qsumCustSales] with SQL like:

SELECT CustomerID, Sum([UnitPrice]*[Quantity]) AS TotalSales
FROM Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID
WHERE Year(OrderDate) = 1996
GROUP BY CustomerID;

Then, you would open your report's record source in design view and add the
query [qsumCustSales] and join the CustomerID fields. Then add the
[TotalSales] field to the query grid so you could use it to sort in the
report.