ordering by overall sales

[tsison7]

I have a report which sums the sales by each customer over the current year.
I have it layed out so that the summary is in alphabetical order by customer
name which is easy. But I also want to have the summary in order of overall
sales...the problem is overall sales is a sum of all the orders and I don't
know how to sort by a function on a report....any suggestions?

 

[Allen Browne]

To be able to order the report by the sum of sales, the source query must
supply that sum to the report:

1. Create a query using your sales table(s).

2. Depress the Total button on the toolbar in query design.
Access adds a Total row to the design grid.

3. In the Total row under the CustomerID field, choose Group By.

4. In the Total row under the SalesAmount field, choose Sum.

5. Save the query.

6. Open the report in design view.

7. Set its Record Source property to the query you just created.

8. Open the Sorting And Grouping dialog (View menu), and sort by the
SumOfSalesAmount field.

If you did want to list all the invoices for the customer as well, perhaps
you could us a subreport for that.

 

[tsison7]

Thanks, Allen I'll give it a try.
--
TIA

To be able to order the report by the sum of sales, the source query must
supply that sum to the report:

1. Create a query using your sales table(s).

2. Depress the Total button on the toolbar in query design.
Access adds a Total row to the design grid.

3. In the Total row under the CustomerID field, choose Group By.

4. In the Total row under the SalesAmount field, choose Sum.

5. Save the query.

6. Open the report in design view.

7. Set its Record Source property to the query you just created.

8. Open the Sorting And Grouping dialog (View menu), and sort by the
SumOfSalesAmount field.

If you did want to list all the invoices for the customer as well, perhaps
you could us a subreport for that.

 

[tsison7]

Allen,

I tried that but because I have other columns in my query (ie. Ship_date,
customer_name), I need to put something in their "Groupby" rows and now
customers are showing up multiple times in the summary....what am I doing
wrong?

Thanks,

 

[Allen Browne]

Any fields from the Customer table, you can use Group By (or First): since
these fields are the same for any customer, it won't mess things up.

For the fields from other table(s), you need to consider what you want
shown. For example, you can use Min or Max on the date field to get the
earliest or latest date, but you can't show all the dates when it's just a
grouping.

 

[tsison7]

Thanks, got it working
--
TIA

Any fields from the Customer table, you can use Group By (or First): since
these fields are the same for any customer, it won't mess things up.

For the fields from other table(s), you need to consider what you want
shown. For example, you can use Min or Max on the date field to get the
earliest or latest date, but you can't show all the dates when it's just a
grouping.