AnsweredAssumed Answered

Finding Highest Total Value Over Multiple Records

Question asked by aboatright on Dec 14, 2012
Latest reply on Dec 14, 2012 by comment

I didn't know what to name this in the subject line. This is my last day before vacation and my brain must already be in vacation mode because I am stumped on what seems to be a simple solution.


I have 499,375 records representing water bills. I was asked to find the top 5 total users over the course of a year.


The relevant fields I'm working with are:

Customer_ID - Unique identifier for a customer

Transaction_Date - The date of the water bill. Most customers will have 12 bills in my found set.

Usage - The amount of water used during a billing cycle.


I started off by making a self relationship with Customer_ID

Next I had a calculation field (Usage_REL) that was the Usage from the related table. So far so good, right?

I then created a summary field (sUsage_REL) to total Usage_REL.


Using the fields in a summary part sorted by Customer_ID will give me the individual totals for each customer, but we're talking over 40 thousand customers. I can't sort by usage since the summary layout part is by Customer_ID


I'm just going round and round trying to get this answer. What am I missing?