invoice if there are past due amount

[azhar2006]

Hi All,
I just had a quick question
is a business that does lots of invoices and collects lots of payments.
When doing an invoice if there are past due amount, this also shows on the printed invoice.
Currently the thought is to always calculate past due amount based on ALL invoices and payments (for the customer).
Just wondering if anyone ever designs a system like this and has each new invoice be the "running" total. Store past due amount in the table (instead of calculate it).

Scenario One: before all the invoices are paid off there is the possibility that the balance will be added to a new purchase order and therefore NEW invoices will be created. I then need to apply a "Invoice Credit" on the old outstanding invoices to show that that purchase order is "paid off" and transferred to the new purchase order.

Scenario Two: After all the invoices are paid in full, the account may make another payment but has not yet purchased anything, Hence the "Payment Credit". this will then be applied to one or many invoices at a later stage.

His thoughts and examples
Thank you
AZ2006



I also was trying to decide if I should create a Table to show a "Ledger of activity" for the Account.
This would show entries each time I create an invoice (a negative debit amount). Or a payment (a positive credit amount), a running total as well as if the entry is an "Invoice", "Payment" or "Credit". Kind of like a bank statement...
Would this be achieved by a Query or a table?

[June7]

I am familiar with only 2 accounting products - QuickBooks and GreatPlains. GreatPlains is a sophisticated and very expensive accounting system. It documents account activity with transaction entries for debits and credits. Then a monthly statement summarizes activity since last statement. This includes balance forward, new invoices and credits, past due fees. I expect the algorithm to calculate balance aging and assess fee is complicated. I suppose QuickBooks would work the same way, I never did use it to generate invoices and monthly statements.

Regarding the running total, it should be calculated when needed. Do not save aggregate data to table. This example http://allenbrowne.com/AppInventory.html is for inventory control but the idea is the same. Summarize then calculate difference.

[azhar2006]

Is this the correct tables and relationships
Thank you

[June7]

Personally, I would not try to connect payments and invoices. I would just apply payment transaction to account and calculate balance as the difference between total invoices and total payments/credits - this is in line with Allen Browne example. Of course, this assumes purchases are allowed on credit (buy now, pay later). What if user makes one payment to cover several invoices? What if they over-pay?

[orange]

Here is a link to another free data model re customers and invoices

I'm not sure why you identify tables InvoiceDate, OrderItems and Payment???

[June7]

Maybe that should be InvoiceData

[azhar2006]

Orange Thank you
So what is the right thing
A certain date each bill is not it ?

Secondly
I want to create a table stockpiling of my money
For the purpose of withdrawal of that money
Where do I put this table and what associated ?

[azhar2006]

Aowoowoowo
Orange Dear Cat
My friend Do you want me to re-building of a new db

[orange]

No. I'm just identifying an existing data model that deals with Customers, Orders and Invoices. There may be some aspect of the model that helps you. The model is intentionally generic and may or may not apply to your situation. You can use it, or parts of it, or none of it.

You are asking us if your model is correct:
your post#3

Is this the correct tables and relationships

We don't know your business. We are having a hard time understanding you.

If I haven't suggested this tutorial before, I am suggesting it now to make sure your tables and relationships support your business.

[azhar2006]

Thank you Orange
Your words are true
I bought the book (step by step Access 2003) and I learned a lot from it
He was talking about the agricultural company
But it is as you said
Thanks again for the brochure

[orange]

You may want to review the videos listed here