update OldCost to NewCost but store OldCost as OriginalCost

[Mitchell_Collen via AccessMonster.com]

This is complicated for me. Please help me if you can.

I want to update a table with NewCost only on drugs that the cost has changed.
On the others I want to leave as OldCost. However, I want to keep the OldCost
so that I can run a query to show the savings in that month on whatever cost
was changed. Is there a way to store OldCost once the NewCost is entered?
Then overwrite OldCost when another NewCost is added.

For instance.
Drug 8343 original cost 49.50
Drug 8343 new cost in Jan 37.00
Drug 8343 new cost in April 32.00

I was thinking of creating a dynamic table to update these but then I thought,
how would I track all drugs that aren't changed also. I want all that
together. I am open to any ideas. New table or new way to query this. Please
help me. I know this is weird one.
Misty

 

[KARL DEWEY]

Keep a running record of price changes like this --
Item Price PriceDate Manufacture
1 1.5 2/2/2003 A
1 0.7 2/11/2003 B
1 2.25 3/3/2003 C
1 1 4/5/2003 D
2 3 5/22/2003 A
2 3 6/1/2003 B
2 3 6/8/2003 C
2 3 2/1/2003 D
3 4 2/22/2003 A
3 4 3/2/2003 B
3 4 3/11/2003 C
3 4 4/3/2003 D
3 4 5/5/2003 E
Use this query ---
SELECT Pricing.Item, First([Price]+0) AS [Price as of],
Max(Pricing.PriceDate) AS [Pricing Date]
FROM Pricing
GROUP BY Pricing.Item
HAVING (((Max(Pricing.PriceDate))<=CVDate([Enter date of price check])));