Payment totals by customer

[wplate]

I've been trying to work this out and I just can't figure it out, sorry if it is really lame-brained.

I want to figure out total payments received from customers, comparing customers against each other. For example I want to quickly determine our top five customers in 2005.

I have a customer table, each record has a unique customer ID. I have a payment table, each record has a unique payment ID. When a payment record is created, it looks up the customer ID (from the invoice) and copies it to the payment record. So this should be easy but my mind can't find the answer.

I think I want to search a date range in the payment table and then subtotal by customer ID (I would prefer to display the customer name instead of the numeric ID, which could be accessed via the relationship to the customer table) sorting by the total of each customer's total.

I'm almost there with this, I can feel it, however there is something blocking me.

Edited December 1, 2005 by Guest

[aaa]

Hi, wplate.

Create relation from Customers to payments by date range and Customer_ID, then in Customer table create Calc field: Total_On_Range=Sum(Payments::Amount), then sort by this field your Customer table. I hope this will help.

[wplate]

Thank you for your reply. I don't get the part of making a relationship based a date range.

I created a text field where I entered a date range and had that field relate to the payment date in the payment table (along with customer ID), but I'm guessing that's not right since the calculated total you suggested remained empty.

So how to put a date range in the relationship?

[aaa]

Hi, wplate!

I builded example in the attachment.

Total_Payments.zip

[wplate]

Wonderful! Thank you for the example, this is very helpful.