Need some help to start corectly a database! (1 Viewer)

[lapadatcristi]

Hello comunity!

I have 3 tables

Table Supplier
SupplierId
Supplier
Adress

Table SupplierInvoices
InvoiceID
SupplierID
Data
SupplierInvoice

TableSupplierPayments
PaymentID
SupplierID
Data
SupplierPayments

The relations: 1 to many from Suppliers table to Payments and Invoices. Linked field: SupplierID


My final result should be a query like

SupplierID
Data
SupplierInvoice
SupplierPayment

So, I want to have a final query/table with lines for each payment and each invoice/supplier in order to calculate how much do I own at a moment to a supplier.

Until now I was working with 1 table that contains payments and invoices also, but I think that this datas should be sepparated.

The final result shoud be like:

Date Payment Invoice
Supplier1 01.01 80
Supplier1 02.01 30
Supplier2 02.01 100
Supplier3 03.01 90
Supplier2 03.01 40
Supplier1 04.01 20

I want this to be able to calculate how much it is my debt to that supplier at one moment.

Can you help me!

PS; Do you think that it is right to create separate tables for invoices and payments? Or to create 1 table that contains Payments and Invoices?

 

[lapadatcristi]

I reattach the picture with the final result because when I write it I lost the order!

 

[Minty]

Your payments should be in a separate table, as I am guessing a single payment may apply to multiple invoices? Or may only partially pay an invoice.
You will need a junction table to allocate the payments to the invoices.

Have a google and search on here for invoicing and payments, it's a very common question and design issue.

 

[lapadatcristi]

I don't need to link a payment to an invoice. It is about overall situation. The payments are from the total of remaining invoices/amounts unpaid for that supplier.
The reason it is to be able to have the balance for 1 supplier at a moment in time.

Is there a solution?

 

[plog]

I want this to be able to calculate how much it is my debt to that supplier at one moment.
...
PS; Do you think that it is right to create separate tables for invoices and payments? Or to create 1 table that contains Payments and Invoices?

Yes, 1 table for Payments and invoices. Further, the payment amount and the invoice amount values should share the same field (e.g. [Amount]), that way you can use a simple aggreate query to SUM that field and get your balance. That new table would also have the fields that SuppliersInvoice and SuppliersPayments share (SupplierID, Data, Description). Then for [SupplierInvoice] and [DataRegCasa] I would try and get that data into the same field, but if not I would include both those fields in that new table and just leave the appropriate one blank when working with the other type.

 

[Minty]

Well in the long term you might well want to. Take this scenario -
Supplier ABC1 has three invoices;

Supplier	Invoice No	Inv Date	Amount
ABC1	1234​	01/09/2020​	£ 100.00
ABC1	1238​	03/09/2020​	£ 78.45
ABC1	1245​	04/09/2020​	£ 100.00

They send you £178.45 because Inv no 1234 is in dispute.

How do you identify which invoices they have paid? You might remember, but in 2 months time when you are chasing them for payment you might not, and someone else wouldn't know.

If all of the above doesn't matter (but it should) then simply using a couple of totals queries add up the payments in one query for all suppliers and the invoices in another query and then join them in a third query.

 

[The_Doc_Man]

I'm going to suggest something that down the road might make your life slightly easier. Your table SUPPLIER has a field SUPPLIER. This is usually not a good idea to have the same names for tables and fields in that table. Not that Access can't handle it, but it can be a source of confusion that you have the name SUPPLIER somewhere and you have to stop to determine which one you meant.

The "goal" you showed us in your #2 post is easy to achieve with the tables you have, but easier to get a nice, neat display if those two tables SUPPLIERINVOICES and SUPPLIERPAYMENTS were somehow merged.

You would merge them simply by adding a field to show what kind of field you had.

Table tSupp: (SuppID {prime key}, Supplier, Address)
Table tInvPmt: (IPID, SuppID, IPDate, IPAmount, IPType)

In this, IPType could be as simple as "I" for invoice and "P" for payment. The IPAmount would always be written as a positive number, but you COULD make a query for it to adjust the signs when trying to determine balance.

SELECT Supplier, IPDate, IPAmount, IIF( IPType="I", "Invoice", "Payment") As IP
FROM tSupp INNER JOIN tInvPmt ON tSupp.SuppID = tInvPmt.SuppID
ORDER BY tSupp.SuppID, IPDate, IPType ;

That would give you a display with the supplier name in order of their supplier number, then within supplier it would be ordered by date. That last element of the ORDER BY is so that if you have an invoice and a payment, the invoice will be seen before the payment. That is pretty close to what you were asking for.