Update field in one form with data from related form

[1953hogan]

Here are my two tables:

Table #1: Inventory
Fields:
ID
Description
Price

Example records in Table1:

ID: 1
Description: Bicycle
Price $100.00



ID: 2
Description: Helmet
Price: $50.00

Table #2: PriceHistory
Fields
ID
ItemID
PriceDate
Price

Examples of related records in table 2:
ID: 1
ItemID: 1
PriceDate: 5/1/11
Price $100.00

ID: 2
ItemID: 2
PriceDate: 5/1/11
Price $50.00

ID: 3
ItemID: 1
PriceDate 6/1/11
Price: $150.00

ID: 4
ItemID: 2
PriceDate: 6/1/11
Price $60.00

Table1 ID field is joined to Table2 ItemID field (one to many with table2 being the "many" subform).

As prices change for an inventory item, a new record is placed in table2 showing the date and the price as of that date (per the sample records). Table2 therefore holds a complete history of the pricing of every item in table #1.

What I would like to do is to write an update query that updates the PRICE field of every record in Table1 with the MOST RECENT PRICE for each item from Table2.

Example:
The price for "Bicycle" should be updated to $150.00, and the price for "Helmet" should be updated to $60.00

Can somebody help me with this? Thanks so much!

Steve

[rpeare]

The simple solution is to not store the price in your inventory table, there's no point to storing it in two locations if you only care about the most recent price. I am assuming your prices will never go down so when you want to run an inventory total just connect the two tables through the ID field and choose the MAX(price) from your PRICEHISTORY Table.

If your prices *may* go down you can still do it this way but it would also be an easy fix. On which ever reacord in your PRICEHISTORY table you want to figure your inventory value by have another field called 'currentrecord' which is yes/no, and when you link the INVENTORY table to the PRICEHISTORY table only choose the records that are marked YES (or -1) from the CURRENTRECORD field.

[1953hogan]

I see your point and will take your advice. Thanks!