Copying values of fields from one table to another.

[taicho]

Ok so here is the problem I need help with,
I have a prices table that stores all the different price changes for each part I have in my Parts table what I need to figure out how to do is to copy the "Last" price for that part from the price table into the Price field of the Parts table or have some way of updating it automatically either by macro or what not - example:

Price Table:

PriceID (PKey)
PartID (Connected to Part Number ID)
Price
Price Added( Date & Time when price was added so I can groupby "Last" or "Max" in the Price Query I have made)

------------------------

Parts Table:

PartID (Pkey)
Part Number
Price

-------------

Now how do I take the value my Price query gives me for "Last" or "Max" Price using the last date for that parts price and have it automatically get copied into the Parts Table price?

Thanx

 

[statsman]

At first glance I would suggest you turn your current query into a new table query.
Create a new table with the new prices.
You can then append the new info into your existing table.

 

[Pat Hartman]

The proper way to do this is to have from and through effectivity dates for the price in the price table and no price field in the parts table. Use a through date of something like 12/31/2100 as the default. Having both a from and through date will make querying easier.

 

[taicho]

Thanks for the replies guys, I have another post called "Database help much needed" that I probably should have just stuck to instead of this one, maybe you can read my last post in "Database help much needed" and give me your opinion(s).

Any help much appreaciated!