Update field in a table with data from a calculated field in a query. 1

[theSizz]

I have a table named {tblItems} and another named {tblVendors}. Both tables are in the same database. A query that's linked to {tblVendors} produces a calculated field called BestPrice. I want to get the data from BestPrice into a designated field in the table {tblItems}. Any help would be appreciated.

Thanks,
Rich

 

[scriverb]

Paste this into the SQL property of a query:

UPDATE tblItem INNER JOIN qryBestPrice ON tblItem.ItemNumber = qryBestPrice.ItemNumber SET tblItem.BestPrice = qryBestPrice.BestPrice;

You may have to change the names of the joined fields to what they are called in your query and table. But, basicially it is going to take perform an inner join on tblItems and your query qryBestPrice on the matching itemNumber fields and then update the BestPrice field in the tblItems.

Does this help? Come back if you need more.

Bob scriver

 

[theSizz]

Thanks Bob,
The inner join was what I was missing. Modified your example to my field names and everything works fine. Thanks for pointing me in the right direction.

Regards,
Rich