Running Balance

[mastang13]

Your help would be greatly appreciated!

I have a typical invoice db (CUSTOMER,INVOICE,INVENTORY,LINEITEM,PAYMENT and CONTACTS db's)and all is well except i need to track over paid invoices. So if a customers invoice total is $500.00 and the customer pays $1,000 I need to be able to apply the remaining $500 towards next purchase. I tried but just can get it to work. If anybody can give me a sample db structure or ideas that would be great!

Thanks

[David McQueen]

You probably have to go to a model based on transactions instead of just purchases (Invoice Line Items). Then there is a record for each purchase ( Money Owed) and a record for each payment (Money Received). The balance then includes all payments and all purchases.

HTH

Dave McQueen

[mastang13]

David,

This is an entity chart of what I have, what am i missing?

||- = many to one

-|| = one to many

Contacts||-Customers-||Invoices-||Lineietms||-Inventory

Invoices-||Payments

So when a customer makes a payment greater that total of invoice I need to track the difference and apply it towards the next purchase for this customer.

Not sure what you mean by transaction?

[David McQueen]

The model I put forward probably won't handle payment assignments to multiple invoices as is.

When I was saying go to a transaction model I was indicating separate records in the same table for invoice line items and for payments. Invoice line items would be positive and payments would be negative and this would allow you to have an instant balance on the account at any time. It does not however assign any particular payment to any particuar line item in the child table or to any particular invoice in the parent table. Hence the model as it stands is deficient for your purposes.

I am not sure what the best approach here would be. It does appear that to some extent you ware going to have to build a double entry book keeping system. I am sure that there are those on the list here who have done it and could chime in.

Dave McQueen