Remaining balance (1 Viewer)

Khalil Islamzada

Registered User.
Local time
Tomorrow, 03:58
Joined
Jul 16, 2012
Messages
49
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
 

Attachments

  • Customer management DB.accdb
    652 KB · Views: 181

bob fitz

AWF VIP
Local time
Today, 23:28
Joined
May 23, 2011
Messages
4,717
IMHO it would be better not to save the calculation to a table. Do the calculation whenever and wherever it is required.
 

Khalil Islamzada

Registered User.
Local time
Tomorrow, 03:58
Joined
Jul 16, 2012
Messages
49
Thanks Bob,

Can you tell me how to do it plz?
 

Mihail

Registered User.
Local time
Tomorrow, 01:28
Joined
Jan 22, 2011
Messages
2,373
Can't open the DB. I use 2007 version.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:28
Joined
Feb 28, 2001
Messages
27,001
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.

Code:
SELECT CustID, -Payment AS Balance FROM tblPayment
UNION
SELECT CustID, Billed AS Balance FROM tblBilling ;

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

Code:
SELECT CustID, SUM(Balance) FROM qryROLLUP GROUP BY CustID ;

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.
 

Khalil Islamzada

Registered User.
Local time
Tomorrow, 03:58
Joined
Jul 16, 2012
Messages
49
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
 

Attachments

  • Customer management.accdb
    1.7 MB · Views: 199

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:28
Joined
Feb 28, 2001
Messages
27,001
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.
 

plog

Banishment Pending
Local time
Today, 18:28
Joined
May 11, 2011
Messages
11,613
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
 

Users who are viewing this thread

Top Bottom