Report Sorting Question

[filthepitome]

I have search through the forums, and the closest thing I can find is this example

http://www.tek-tips.com/viewthread.cfm?qid=1044612

I have this information

I have detailed customer data (ID, Sale amount, Sale Item, etc...) I have grouped by CustomerID so that I can give a summary in the group header (Total Sales amount, total items, etc..), then in the detail section list each detailed sale.

I currently have it sorted by CustomerID, then by the date of the sale in the details. What I would like to do is sort by customer with the most sales. In the grouping/sorting diaglogue I tried the expression

SUM([Item Count])
and
=SUM([Item Count])
I have a field titled "Item Count", each record displaying 1.

I get an error each time I try to run it though. Can what I am trying to be done? Grouping by their ID, but sorting by their total items?

TIA

 

[dhookom]

Do as suggested in the previous thread. Create a totals query the groups by CustomerID and calculates the value you would like to sort by. Add this totals query to your report's record source and join the CustomerID fields.

This will allow you to place the calculated field in the report's sorting and grouping dialog.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]