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?