How to use selection criteria in an update query?

[Guest]

I have two tables of pricing data that are by item for Jan through Dec;
one of current prices with the second of future prices. I am trying to use
an update query to update the monthly buckets in the base table with the
prices from the future table; however I only want to update the monthly
prices in the respective item record where the future prices are other than 0.
I put the criteria ">0" in the criteria section and staggered it down
(not on the same row for each month) in an attempt to have the query only
update prices for those months where the price in the future table not equal
to $0.
Currently, the query is updating all records in the base table vs only
those records where there is a matching record in the second table.
Thoughts or comments would be welcomed

 

[Guest]

When you staggered them down you were saying 'I do not care which of these
months are > 0.'

You have bad structure. You should have Item, MON, Price fields.

But to use what you have, place an IIF statement in the Update To of the
grid like this --
IIf([FuturePrices].[MonthPrice] >0, [FuturePrices].[MonthPrice],
[CurrentPrices].[Price])