Remaining balance

Dear All:

I want to develop a database for customer management as below details.

1. Customer tbl (contain cusomter info)
2. Billing tbl (contain billing info)
3. Payment tbl (contain payment info)

Now, in billing form I have a field called "remaining ", I want it automatically calculate the total billed minus total payment and the balance appears in this field.
The database is attached please if anyone can help me in doing in attached file , I will be very happy and appreciate your cooperation.



Best,
Khalil

IMHO it would be better not to save the calculation to a table. Do the calculation whenever and wherever it is required.

Thanks Bob,

Can you tell me how to do it plz?

Can't open the DB. I use 2007 version.

In general, you can treat that as a limited type of transaction system.

You would make two queries. I'm going to simplify so think about what I suggest before actually trying it. If you don't understand it, come back and ask.

Do something like this. Define a working query to aggregate your data. Let's call it qryROLLUP.


That IS a minus-sign in front of the Payment field name.
Now use THAT query as the basis for your totals


I am doing a quick-and-dirty here, so play with it. There are ways to embellish it, but the above is the basic idea.

Hi The_DOC_Man:

Thanks for your reply, it is a bit confusing for me, can you plz do it in attached database and upload it again, I would be very happy.


Many thanks
Khalil

Unfortunately, my situation doesn't allow me to read that database. You can create the two queries with the Summation query calling the UNION query. Then, if you open the Summation query, you would see the customer IDs and their balances, positive or negative.

I'm dating myself, but hopefully you'll get the example:

You don't have a log book for your checking account where you register all the payments you make and then a completely seperate log book for all the deposits you make do you? No, you have one checking log with both credits and debits.

This is how any system of credits and debits should work--they should be stored together. That way, you can simply do math (SUM([TransactionAmount]) ) and easily get the balance. You need to structure your tables so all transactions (initial billing and subsequent payments) are in the same table.

that table is going to look similar to this:

Transactions
tran_ID, ID_Cust, tran_Type, tran_Date, tran_Amount
1, 2, Billing, 1/1/2016, 500
2, 3, Billing, 1/4/2016, 200
3, 2, Payment, 1/5/2016, -100
4, 2, Payment, 1/30/2016, -200
5, 3, Payment, 2/2/2016, -50

That way a simple query will get you the current balances of your customers. In fact it's so simple this is it:

SELECT ID_Cust, SUM(tran_Amount) AS Balance FROM Transactions;

ID_Cust, Balance
2, 200
3, 150