History table (1 Viewer)

[geno]

Hi,
I am working on an order database, the products table is updated weekly with correct pricing through ODBC from a POS software that my company uses. If a product was on special one week and then the table is updated, the price will change back to the regular price. I can see problems as when reports are run for total sales, the data will vary with the special price reverting back to a regular price. What I think I need is a history table that will hold the correct pricing when the order was made. Then the reports will look at this history table and will be using the correct data. Can someone point me in the right direction.
Thanks

 

[pono1]

Geno,

I think you are moving in the right direction. Add to the history table each time a price changes and you will have access to data that will help you chart prices through time. Alternatively (or additionally), do you have a table that saves sales transactions? That's a place where you might break from DB normalization rules and create a field to save the sales price within each transaction record since, as you noted, depending on the transaction date, the price of a product will vary.

Regards,
Tim

 

[geno]

Hi, I've created an append query that takes all the fields that I need for the history table, I think it would be best for this query to run on closing of the database. The problem being if the user re opens the database and closes it again the info would be appended again to the history table. How can I look at the table to see if the record already exists then only append records that are not in the table?

 

[geno]

Hi, I think figured out the best way to do this, I included the primary key field from the orders detail and made it no dups.
This seems to work.