I assume you already have a customers table, a transactions (sales or purchase or whatever) table and a payments table among others.
I also assume your payments table includes fields like: ...TransactionID, AmountPaid, DatePaid....
And that customers table includes: ....CustomerId
And that Transactions table includes: ...TransactionId, TransactionDate, TransactionAmount, CustomerId
Now you need another table like PayTrack(Id, CustomerId, TransactionId, DueDate, DueAmount).
Whenever a transaction is made
1. insert into transactions.
2. insert into PayTrack the transactionID and customerID and set DueDate = Transactions.TransactionDa
Whenever payment is made, do the folowing
1. insert into payments making reference to TransactionId and CustomerId
2. Update PayTrack by setting DueAmount = DueAmount - Payments.AmountPaid Where customerID and transactionID correspond.
Whenever DueAmount > 0 Then clients still owes if Duedate <= currentDate.
If DueAmount < 0 Then YOU owe the client (that is client has overpaid)
If DueAmount = 0 then client does not owe you and you do not owe client
I think that covers the basics of what you want... but let's know if there is something missing...
Main Topics
Browse All Topics
by: rg20Posted on 2009-08-11 at 06:34:06ID: 25068828
you would have your customer table
customerid, customerName
Next is a payment table
customerID, paymenttype, paymentamount
when you want to get the payments you could\
select * from customer inner join payment on payment.customerid = customer.customerID where customerID = <whatever the customer id is>