Creating an invoice system with Order & Invoice Tables with item lines for each

[QA_Compliance_Advisor]

I first thought rather re-invent the wheel i would download a template from ms access. However, there is an issue with the whole system. The templates allows for products information etc and allows for a product to discontinue, however when you discontinue a product it removes itself from the lookup queries for products and thus not displaying in the orders Item Line and Invoice Item Line. A result which is difficult to display previous invoices as the lookups have removed the discontinued product - Seems to be something overlooked.

So if a product is discontinued when you click an invoice to view it, if it has a discontinued product it will not display due to the set with the lookup queries.

Is here any thoughts how to overcome this.

I first thought that I would have an orders & item line tables relating to each other & have the same for an invoice and item lines tables relating to each other. & go this process;



1) complete an order with populated product info and then once the order is complete -
2) copy all the info and have it all stored into the invoice table against its respected orders & order item lines.

this would result in have two different systems;
1) with a dynamic system which updates available products in lookup queries & copies that info to invoices etc
2) have a 2nd system- Invoices - which stores all info which does not change.

Is there any thoughts to this and how would one implement it, ie copying a recordset from 2 tables with lookups to another 2 tables?

[ranman256]

If a product is discontinued, it should still be in the invoice.
but it should also be in the products table but flaged as discontinued = true

lookups cant happen for invoice entry , but can by the admin or a read-only invoice review.

[pbaldy]

I would not have two systems. I'd have a flag for "discontinued" or whatever in the table. A combo for new entries would exclude based on this flag; other views would not.