Capturing Historic Data

[SASHA_D]

Hi Everyone,

I am hoping someone can help!...
I have 6 tables that I join together using a SQL Union query (there is no id that links the 6 tables to each other, but they link to other tables)
Each table is from a supplier and contains the fields 'Supplier Name' 'product ID' 'Net Price' 'Invoice Price'.
The union query is matched up to three other tables using the 'product id' field to bring back other information associated with the product.
My problem is how to build up a history of prices for the products.
The 6 tables from each supplier will be coming in every month-at the moment I just have the current month, however I need to be able to store monthly prices and show whether a price has changed from the last month.
Not sure how I can do it-I've thought about in some way including a month field on each of the 6 tables, then appending data to a table each month, but then how would I actually view the data? Ultimately I'd like to have some sort of table with a graph, showing the last 5 or 6 months prices!

Can anyone point me in the right direction with this??

Many thanks,

Sasha
London, UK

 

[Len Boorman]

Hi Sasha
Just some initial thoughts
I think you will need a table of prices to use as a base.
say product Id and Price

You could then UNION ALL this table and productId and price from incoming data bnased on product ID.

Then use the Find Duplicates wizard to create a query finding duplicates, Tweak the sql so that the criteria on the count is 1 to find the non duplicates. This should output product id and price and have at least two lines for each. i'e one line is your base price and one line is the same product but with a different price


Len