table design

[mfuada]

hi guys i need some suggestion....
right now i have a table that store a price, the structure is like this:
table : values
fields: date_purchase,item,price

And i also have a report that shows total sum of all item's price, i make it by building a query to sum all the price of every item...
the problem is, i want to track the changes of the price... because time to time the price is changing. and i would like to make a report that have this structure:
date_purchased item price
17/02/08 a 20$
28/03/09 a 30$

and if i stored the new price on the field price, it will changes the total sum of the price that i usually make on my report, because item a is now worth of 30$ not 50$
so is there any suggestion? should i make a new field to store the new price?
thanks...

 

[HiTechCoach]

Sounds like you need to get the Last Date's Price.

I would use a sub query to get the value for the Price.

 

[mfuada]

is there any example to search the last date price? thx?

 

[mfuada]

i think the real problem is because i stored the new price and the old price on the same field, it will disturb the total value for my query that i use in my report....
so should i make another field to store the new price? or is there any idea?
thx

 

[buckau]

try using 2 tables the first to store you book data and the second to stor the prices and make it a one to meany relationship eg one info data recored and meany price recoreds per entery on the first table. that way you will all ways have your new and old data intack per book item for referance.