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]

Usually, you use "DISTINCTROW" when you are trying to weed out duplicates from being returned to a SELECT query.
So, use of that indicates that for each record shown by the query, there may actually be multiple records having those values.
As such, that makes this type on query not Updateable.

If you remove the word "DISTINCTROW", it might be able to be made into an Update Query, provide that there are not other factors going on that would make it not Updateable.
See here for a list of those: Microsoft Access tips: Why is my query read-only?

You may need to re-think what you are trying to do do here.

 

[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

<tbody>
</tbody>

When I run it as Select to ensure it returns the same number of records without the Update Table Joined, it's fine but when I join the Update Table...the records increase from 1140 to 3420 without even joining the tbl???? I'm Joining on Product_Family and Country...

 

[Joe4]

If joining two tables results in an increase of records, it usually means that you either have a one-to-many relationship or a many-to-many relationship.
If you do not think this should be the case (and you think that you have a one-to-one relationship), then that means that there is a problem with your Join. Sometimes, more than one field is needed in your Join.

It might be best for us to see what you are trying to do if you can do the following:
1. Post sample data from your Strat_Price_Tbl table
2. Post sample data from your Channel_Value_TBL table
3. Post your expected results, based on the samples provided above

 

[Mac1206]

This is the latest query which gives the right amount of rows returned but no Channel_Value update...


STRAT_Price Sample Tbl

Product_Family	Product_SubFamily	Country	Channel_Code	Channel_Value
BAR	MR JEAN	US		0
BAR	MR BILL	CA
BAR	MR SMITH	US		0
BAR	MR JEAN	CA
BAR	MR JONES	US		40
BAR	MR JONES	US		0
BAR	MR JOHNSON	US		0

<tbody>
</tbody>

Strat_Price Sample Tbl, I need to update the Channel_Value with the below table....Thanks

Channel_Code	Product_Family	Product_SubFamily	Country	Currency	Channel_Value
Store	BAR		US	USD	0
Markets	BAR		US	USD	40
Markets	BAR	MR TOM	US	USD	0

<tbody>
</tbody>

This query returned the correct rows to be updated but when I view it, it does not update the Channel_Value columns.....

UPDATE DISTINCTROW Strat_Price_Tbl INNER JOIN Channel_Value_TBL ON (Strat_Price_Tbl.Product_Family = Channel_Value_TBL.PRODUCT_FAMILY) AND (Strat_Price_Tbl.Country = Channel_Value_TBL.COUNTRY) SET Strat_Price_Tbl.Channel_Value = [Channel_Value_TBL].[CHANNEL_VALUE]
WHERE (((Strat_Price_Tbl.Product_Family)="BAR") AND ((Strat_Price_Tbl.Country)="US") AND ((Strat_Price_Tbl.Product_SubFamily) Not Like "MR TOM"));

 

[Joe4]

Can you try again, giving us what was requested in the previous post?
I do not want to see failed query attempts.
I want to see what the underlying tables look like, and what you want to expected results to look like.

 

[Mac1206]

Sorry,

I sent you both tables as requested with the Strat_Price Table populated with results sample results loaded....

 

[Mac1206]

Here you go,

Strat_Price Sample Tbl

Product_Family	Product_SubFamily	Country	Currency	Channel_Code	Channel_Value
BAR	MR JEAN	US	USD	Store
BAR	MR BILL	CA	USD	Markets
BAR	MR SMITH	US	USD	Markets
BAR	MR JEAN	CA	USD
BAR	MR JONES	US	USD
BAR	MR JONES	US	USD	Markets
BAR	MR JOHNSON	US	USD	Store

<colgroup><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

Channel_Value Sample Tbl Update Source Tbl

Product_Family	Product_SubFamily	Country	Currency	Channel_Code	Channel_Value
BAR		US	USD	Store	0
BAR		US	USD	Markets	40
BAR	MR TOM	US	USD	Markets	0

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

Results from Update

Product_Family	Product_SubFamily	Country	Currency	Channel_Code	Channel_Value
BAR	MR JEAN	US	USD	Store	0
BAR	MR BILL	CA	USD	Markets
BAR	MR SMITH	US	USD	Markets	40
BAR	MR JEAN	CA	USD
BAR	MR JONES	US	USD	Store	0
BAR	MR JONES	US	USD	Store	0
BAR	MR JOHNSON	US	USD	Store	0

<colgroup><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

Bare with me, I'm sorta new to this but willing to learn with a bit of help...Mac1206

 

[Joe4]

I don't understand your expected results, i.e.

1. Why is the "MR SMITH" record updated to 40, but not the second "MR JONES" record?

2. Why do some of the Channel Code values change from your first table to your expected output?

 

[Mac1206]

Thanks Joe, I will definitely stay within guidelines for sure. Your questions:

1. If you view the update table in the middle, Product_Family, Country, Channel_Code has a Channel_Value = 40, this is my dilemma because Product_Subfamily is blank in the update table but the main Strat_Price Tbl has various Product_Subfamily...You are right, MR JONES met that criteria as well...Typo on my part...

2. It was just a typo on my part creating sample data for you quickly...Thanks