Update Query using matching criteria from 2 tables

[MarcieFess]

Hi all. I think I am confusing myself; it seems to me this should be an easy query to create, but then I second-guess myself and now I'm all messed up.

I have 2 tables that must be involved in this query. Both have the same fields; one is a temporary table created from an Excel import which will be deleted later, the other is the permanent table.

tblStoreProducts is the permanent table. It has a 2-field Unique index; there can only be 1 of any ProductKey for a StoreKey (there can be multiple ProductKeys within a StoreKey, and multiple StoreKeys for each product, but there can only be 1 entry for each unique combination of ProductKey/StoreKey).

tblImportToStoreProducts is the temporary table. It holds all of the ProductKeys for one StoreKey.

My query needs to identify any ProductKey in the tblStoreProducts that does not exist in the tblImportToStoreProducts for that StoreKey, and then it must set the MaxUnits field to 0.

Basically, the temporary table is this years' inventory for a particular store. If a product was in that store last year but it isn't there this year, the quantity must be set to 0 because the ultimate aim is reporting hazardous materials.

Marcie

 

[CJ_London]

You need to use left joins between tblStoreProducts and tblImportToStoreProducts on both ProductKeys and StoreKey.

To create a left join, in the query designer iIf you draw the relationship if it does not already exist between the two tables on these fields. Then double click on it and select the second option which should say 'Include ALL records from tblImportToStoreProducts and only those records from tblStoreProducts where the joined fields are equal'.

Then for your criteria, drag down ProductKeys and StoreKey from the tblStoreProducts table and set the criteria for both to Is Null.

This will limit the records in tblImportToStoreProducts to those where there is no matching record, so you can update the MaxUnits field to 0