Question Invoice-receipt database structure

[atisz]

Hi all,

I have a question regarding the way an invoice-receipt database should be built.
I have made one database of this kind a few months ago, but now I have the following problem:
1. One receipt can be used to pay more then 1 invoices
2. One invoice can be payed with more then 1 receipts

The main goal is to assure an easy input on user side and to get correct reports showing the value of the invoices, the payed amount with which receipts and the eventually remaining amount to be payed.

I tried and tried over and over again to find a solution for this, but without any result.

If someone have an idea how to... please let me know.

Thanks, Attila

 

[RainLover]

What does your table structure look like?

You will need a Many to Many Relationship between Invoices and Payments.

 

[atisz]

What does your table structure look like?

You will need a Many to Many Relationship between Invoices and Payments.

My table structure is not very important, because I want to redesign the hole database, to meet all the requirements, that's why I asked for a structure model.

What I have now is:

tblCustomers
tblProducts
tblInvoices
tblInvoiceDetails
tblPayments

When I built the database I made primary keys for all tables, which links Customers to Invoices, Products to InvoiceDetails, InvoiceDetails to Invoices and Invoices to Payments.

I used for data input frmInvoices which had two subforms, one for InvoiceDetails and other for Payments.

But because of the situation I have told in point 1 and 2 from my previous post, I thought I have to "separate" somehow the payments from the rest, and the real problem now is that I don't know how to link the Payments to the rest of my data.

 

[gemma-the-husky]

the way you have described your system, you also need a payments-matched table as well as the payments and invoices tables, since you are trying to model a many to many link between the invoices and payments tables (ie one payment paying multiple invoices and/or invoices paid by several part-payments).

Basically, you cant directly model many to many joins - you have to split them into 2 one to one joins - hance a payments-matched table which will link each invoice to the appropriate payments, and will link each total remittance, to
the individual invoice payment allocation.

Hope this reasoning makes sense

 

[Rabbie]

I find I can produce reports showing the the current Amount owed the previous invoices and the previous payments using a union query which negates the value of the Invoices then the balance can be produced by either a sub-query or a DSUM function.