Page 1 of 1

Calculate Balance

Posted: 2018-01-28 17:00
by gatenet
Hi guys i need some help
I have 3 tables with these fields
1. table sales
some services fields and at the end i calculate all the prices fields in one [total]
2. table accounting
[date]
[sale] (lookup from table sales with [id] - [customer]
[total] (lookup from table sales with [total]
3. table payments
[date]
[account](lookup from table accounting)
[payment_amount]
[balance]
in table 2 i charge the customer with an amount and then when the customer gives an advance i would like to substrack it from the balance
for example in table accounting the total is 100
then the table payments i put a new record, i choose the customer i put the payment amount of 20 and the balance goes 80
then another day i put a new record in the same customer of another 20 and the balance goes 60

How can i do that?
Thank you

Re: Calculate Balance

Posted: 2018-02-12 20:02
by R Tammam
Hello gatenet,

i would like to tell what i understood to make sure that it's waht you want

you want to insert in payments table (date , accounting value , payment amount , calculate the new balance after subtraction),
i think you can have the first three values directly ,
but for the last one you should
1- select the total value for the accounting value you will insert
2- the balance will be the total you selected in the previous step - the payment amount you will insert in the last step

i hope the steps would be helpful for you

Regards ,
Reham

Re: Calculate Balance

Posted: 2018-02-13 15:14
by R Tammam
sorry , i found something else in the idea , for retiring the total amount , you will need to join sales and accounting tables together as total in account is look up for sales table