I have written an access2003 invoicing mdb that I obviously didn't think through enough and need some help.
"Customers" (which contains the customer specific [discount]) Related to "Invoice" Table
"Products" (Which contains the default [price] for each product) Related to "InvoiceDetail" Table
"Invoice" (Which contains the data that does not change for each line item) Related to "InvoiceDetail" Table
"InvoiceDetail" (Which contains the data for each line item in the Invoice, including the:
1. [price] from the "Product" table and
2. [discount] from the "Customer" table (based a query related to a combo Box in "Invoice" that allows the user to select the appropriate customer)
So, my problem is: All of the forms etc are working perfectly, BUT...
How do I keep the "Invoice" and "Invoice Detail" records from changing when [customer].[discount] or [product].[price] changes over time. In essence, after an invoice and its items have been created, I want to preserve the info that might change over time associated with it so that when I do a subsequent analysis across a date or invoice # range, I get data based on the tables as they existed at the time of the invoice creation...rather than the time of the analysis.
Can anyone help with some direction here?