Help with query Update Access 2013

[Mac1206]

I have a need a update query that will update the Values column for all products_subfamily except "MR TOM", "US" but still update the Values in all other Product_Subfamilies and Blank Product_Subfamiliy in the US which are not MR TOM with the source update table below. Please assist...Thanks in adavance

My Query:

SELECT DISTINCTROW Strat_Price_Tbl.Product_Family, Strat_Price_Tbl.Product_SubFamily, Strat_Price_Tbl.Country
FROM Strat_Price_Tbl INNER JOIN Channel_Value_TBL ON Strat_Price_Tbl.Product_Family = Channel_Value_TBL.PRODUCT_FAMILY
WHERE (((Strat_Price_Tbl.Product_SubFamily) Not In ("MR TOM")) AND ((Strat_Price_Tbl.Country) Not In ("CA")));

CHANNEL_CODE	PRODUCT_FAMILY	PRODUCT_SUBFAMILY	COUNTRY	CURRENCY	CHANNEL_VALUE
Stores	Bar		US	USD	0
Markets	Bar		US	USD	40
Markets	Bar	MR TOM	US	USD	0

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Joe4]

So, should these tables be matching on Product_Family AND County AND Channel_Code?

Note: I can see problems on the horizon - it is never good to have blanks on fields that you are matching on, so blanks in the Channel_Code and Product_SubFamily fields can complicate issues.

 

[Mac1206]

So, should these tables be matching on Product_Family AND County AND Channel_Code?

Note: I can see problems on the horizon - it is never good to have blanks on fields that you are matching on, so blanks in the Channel_Code and Product_SubFamily fields can complicate issues.

Thanks for the response Joe, and Product_SubFamily which the key for the field is on the blank rows except for MR TOM....Yes, that does indeed complicate things...omg

 

[Joe4]

Does this SELECT query at least bring back the correct records and values?

SELECT Strat_Price_Tbl.Product_Family, Strat_Price_Tbl.Product_SubFamily, Strat_Price_Tbl.Country, Strat_Price_Tbl.Currency, Strat_Price_Tbl.Channel_Code, Channel_Value_TBL.Channel_Value
FROM Strat_Price_Tbl INNER JOIN Channel_Value_TBL 
ON (Strat_Price_Tbl.Channel_Code = Channel_Value_TBL.Channel_Code) 
AND (Strat_Price_Tbl.Country = Channel_Value_TBL.Country) AND (Strat_Price_Tbl.Product_Family = Channel_Value_TBL.Product_Family)
WHERE (((Channel_Value_TBL.Product_SubFamily) Is Null Or (Channel_Value_TBL.Product_SubFamily)<>"MR TOM"));

 

[xenou]

@Mac1206 I'm not sure why you'd use distinct or distinct row in an update query.
By definition, that means "don't show duplicates, even if there are some". But if you have duplicates that you aren't showing, then by definition you aren't updating all the rows that match the criteria.

Note: also, it's probably not allowable, since by definition the database cannot decide which of the duplicate rows should be updated and which should not be updated.

 

[Mac1206]

@Mac1206 I'm not sure why you'd use distinct or distinct row in an update query.
By definition, that means "don't show duplicates, even if there are some". But if you have duplicates that you aren't showing, then by definition you aren't updating all the rows that match the criteria.

Note: also, it's probably not allowable, since by definition the database cannot decide which of the duplicate rows should be updated and which should not be updated.

Thanks Joe, everything worked out perfectly....