Sort by Footer Sum

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can a report be sorted by the sum in a footer? I have a report that is
broken out by store and lists the items ordered and total Lbs in the Item
Header. Have a total in the Store Footer that sums the total Lbs. Need the
report to list the store in descending order by total lbs ordered.
 
Supe said:
Can a report be sorted by the sum in a footer? I have a report that is
broken out by store and lists the items ordered and total Lbs in the Item
Header. Have a total in the Store Footer that sums the total Lbs. Need the
report to list the store in descending order by total lbs ordered.
Click to expand...


No. You need to calculate the total in the report's record
source query. This can be done in a calculated field that
uses a subquery:
StoreLbs: (SELECT Sum(lbs) FROM table As X WHERE X.store =
table.store)

Or, probably faster, you can create a separate query that
calculates all the store lbs
SELECT Store, Sum(lbs) As StoreLbs
FROM table
GROUP BY Store

and then Join that to the table in the report's record
source query"
SELECT table.*, qry1.StoreLbs
FROM table INNER JOIN qry1
ON table.Store = qry1.Store
 
Back
Top