Ok my brain started working again.
1. Self relationship using primary key (Customer_ID) <--index it too
2. Create summary field to total usage. (sUsage)
3. Create a calculation field (Usage_REL) that = sUsage from the related table.
4. On your layout, use the Usage_REL field from the related table
Message was edited by: aboatright
In the Customer TO, have a relationship to the Usage TO, which I'm sure you already have.
In the Usage TO, create a field "Usage for 2012" or something like that. Set it equal to: If ( Year ( Usage Date ) = 2012; Quantity ).
In the Customer TO, have a field called something like "Quantity for 2012" and make it a calculation equal to Sum ( Usage TO::Usage for 2012 ).
Then you go to a Customer TO layout and place the "Quantity for 2012" field any where you want and it'll show you the 2012 quantity for each customer.
I can't sort by usage since the summary layout part is by Customer_ID
That's not entirely correct. If you have a summary field defined as Total of Usage, you can sort by CustomerID, descending + Reorder based on summary field [sTotalUsage].
Using a layout with only a sub-summary by CustomerID part, this will display a list of customers ordered by their usage. If you like, you can loop until you get to the 6th group and omit the records from there - thus leaving only the top 5 customers' records in the found set.
No relationship is required for this to work - you only need to find the records of the relevant year and sort them. Note that a self-join relationship based on matching CustomerID alone will include the customer's records from all years.
Note also that sorting and reordering half a million records might take a while.