Invoices table(s) with one invoice refer. to multiple products

[aian]

Hello to everybody,

I want to create a database that (among other things) will be storing invoices coming from suppliers. These invoices have a single number, multiple products with their respective prices and tax (each product can have different tax, 9% or 19%, but that can change in the future).

I have already created a PRODUCTS table, where each product has an ID, a name, a stock count (how many pieces are currently in stock) and a supplier.

My initial thought is to create two different tables:
- One table would include the InvoiceID, date of issue, supplier'sID, total cost (no tax) and total cost (including tax) for all products mentioned in the invoice
- The other table would include the same InvoiceID, the productID, the price (no tax), the tax and the pieces count.

My problems are:

1. is this the correct way to approach the database or am I running towards trouble?
2. if it is the correct way, how do I implement it?


Thank you in advance,

Alexander

 

[neileg]

Hello to everybody,

Hello!

I want to create a database that (among other things) will be storing invoices coming from suppliers. These invoices have a single number, multiple products with their respective prices and tax (each product can have different tax, 9% or 19%, but that can change in the future).

Understood

I have already created a PRODUCTS table, where each product has an ID, a name, a stock count (how many pieces are currently in stock) and a supplier.

Not a good idea to store balances, better to calculate them from stock in minus stock out whenever you need them. Search these forums for inventory or stock control for more guidance.

My initial thought is to create two different tables:
- One table would include the InvoiceID, date of issue, supplier'sID, total cost (no tax) and total cost (including tax) for all products mentioned in the invoice
- The other table would include the same InvoiceID, the productID, the price (no tax), the tax and the pieces count.

My problems are:

1. is this the correct way to approach the database or am I running towards trouble?

Right approach, almost. You should not store the value totals in the header table, these should come from summing the detail table.

2. if it is the correct way, how do I implement it?

Clearly, you need the header and detail tables linked by the invoiceID. Data entry should be by form/subform. The header data is in the form, the subform should be a continuous form holding all the detail lines that relate to the invoice. Access will handle the parent/child links and ensure that records added in the subform will be populated with the invoice ID.

Good Luck

 

[aian]

Dear neileg,

thank you for your quick answer. I didn't know the exact terminology for inventory or stock control...

I'll revert if I get stuck...

Alexander