Customer Balance

[Khalil Islamzada]

Dear All

I have a table called transaction with below fields;

Cust_id, Debit, Credit and Rbalance (remaining balance) columns.

I created a query and can have the balance of all and each customer, now I have a form and want automatically calculated balance appears every time I create a bill for each customer.

I have uploaded the DB please check if you can do it in DB and upload it again or show me any solution, I appreciate.


Thanks,
Khalil

 

[plog]

Cust_id, Debit, Credit and Rbalance (remaining balance) columns.

That's how you do it in a spreadsheet, not a database.

1. You shouldn't store the balance, instead you should calculate it. That means Rbalance shouldn't be a field in your table, but should be a field in a query.

2. Debits and credit values should be stored in the same field of a table. That way you can use the SUM function of SQL to determine your balance.

So, Transaction should look like so:

Transaction
TrnID, autonumber, primary key--don't use symbols for field or table names
customerid, number, foreign key to customer--make this required
TrnAmount, number, will hold value of transaction--negative denotes a debit
TrnDate, date, replaces dates field--more specific naming
TrnRemarks, long text, replaces Remarks--more specific naming

That's it. Then the query to determine the balance for a customer is this:

SELECT customerid, SUM(TrnAmount) AS Balance FROM Transaction GROUP BY customerid

Then to get a specific value into a form, you use a control and DLookup into that query using the customerid.

 

[Khalil Islamzada]

Dear Plog;

Many thanks for your advice, is it possible for your to do the query and dlookup in database I uploaded that I can use it, because I am not that familiar in access.


Many thanks
Khalil

 

[plog]

No, because my solution requires a reorganization of your tables. You need to structure your tables as I illustrated.

 

[Khalil Islamzada]

Dear Plog,

As you said I illustrated the DB and uploaded, now I have two questions;

1. In transaction form, where should I add remaining balance of the customer?
2. How to do Dlookup?

Many thanks for your help in advance.

Khalil

 

[plog]

1. Where ever you want it to appear. You place a new input area on your form where you want it then set the Control Source property to the Dlookup.

2. Dlookup reference is here: https://www.techonthenet.com/access/functions/domain/dlookup.php

Dlookup takes 3 arguments:

=DLookup("[FieldNameHere]", "DataSourceNameHere", "CriteriaHere")

You need to fill in those 3 values with the information specific to your database.Give it a shot then post back here what you tried if you have trouble.

 

[Khalil Islamzada]

Dear Plog:

Thanks again for your helps, I did the following;

1. In form created a control box
2. I did the dlookup formula as you said

But as you can see in attached database,

It is good that in a new record you will see the remaining balance of each customer.

But the problem is if you see customer number 2 the first bill was 50000 and in second transaction he paid 65000, so If I want to reprint or view his previous bill it shows the current balance for all previous bills, while I need balance of each bill up to that time.

hope you get my question and problem.

I attached the database again and you can see.


Thanks again,
Khalil

 

[seguinsoftware]

Debits and Credits should be separate fields if you are thinking in accounting terms. Each should be positive values and the type of account you are posting to determines whether you subtract or add to the debit/credit balance, then the credits-debits give you the balance. You can do a running total for balances.