I have a one to many data model. specifically, a Customer table and a Payment table. I want to have the balance of each Customer in an extra column in their table.
Customer: Customer_ID, Name, Fee, Balance
Payment: Customer_ID, Amount
And the balance would be sum of all of the payment Amounts of the customer minus the Fee. (This is not a perfect design, but works fine for my case.)
How can I get the balance? I have tried joining two tables using "relationships", VLOOKUP, SUMIF. But either these are not the right tools or I don't know how to use them.
Is there a simple solution without using Macros?