Group and Sum

[leftcoast]

Hello,


Problem Is: Trying to roll up multiple account numbers for various customers and total two sales columns.


Expl. Customer “Y” has three accounts. Customer “x” has four accounts. Etc.
I need to sum these so that I have one total for each customer over a specific time period.



The goal is to use this in a report so that I can sort this total field to determine the top accounts over a specific time period. Access will not allow you to sort on sum fields in reports.



Any ideas on how to accomplish this?

[June7]

I just tested sorting on a Sum field in a report and it worked. I tested both in the SQL and OrderBy property of the report.

[leftcoast]

I am not sure how you got that to work.

What I have found is that calculated fields created on the report do not show up as a "Sort" item. It is my understanding that this has to be done in a query and then it will show up as a sortable item. So, the question is how do you sum a group of various customers with multiple records in a query to get totals for each group of sorting on the report?

[June7]

Okay, calculation in textbox is not a sortable field, must be in query, which is what I tested. The report I tested used an aggregate query. You want to report record details and use report Grouping & Sorting with calcs features? I guess that is the conundrum, retaining details and reporting aggregates.

Without knowing your data better, hard to be sure, but possibly you could build the aggregate query and join it to the record detail query on the customer ID. This will give each record a field with the aggregate value, include this field in the sort criteria. Or use DSum domain aggregate function to return the aggregate value for each customer in a field. Some people don't like the domain functions, they can be slow.

[leftcoast]

I appreciate you taking a look at this. I have been working on this for some time without getting any place with this except frustrated!!

Below is a sample of the data. Each customer can have multiple account #’s and so if you are trying to get the data summed in a group by customer over a time period and then sorted by greatest to smallest customer total on the report that is where the problem is. You would think that would not be a big deal but it sure has turned out to be one! I hope this gives you more information to work with, sure can use the help.

CustomerID Customer Account# Month Booking1 Booking2
1 XYZ A123 Jun2011 $250.00 $10.00
2 XYZ C789 July2011 $100.00 $50.00
3 ABC R522 Jun2011 $50.00 $25.00
4 ABC T322 July2011 $75.00 $60.00

[June7]

I would have to build table and try queries. Before going there, did you try the suggestion I offered? Post your attempted queries.