Access 2013 updating over and over again

[Mac1206]

Hi, I have a update query in which I only want to update all records within my main table that is equal to the criteria set in the second table of USD. My problem is that since the CURRENCY field doesn't exist in the main table, the update query loops over and over again. My main table has 126007 but when I try to update, the query is updating 997097 records. See below the update query...Thanks in advance....

UPDATE Calculation_Strategy_Price_Tbl INNER JOIN Strategy_Off_Invoice_Rebate_Tbl ON (Calculation_Strategy_Price_Tbl.CUSTOMER_SOLD_TO_NUMBER = Strategy_Off_Invoice_Rebate_Tbl.CUSTOMER_CODE) AND (Calculation_Strategy_Price_Tbl.PRODUCT_FAMILY = Strategy_Off_Invoice_Rebate_Tbl.PRODUCT_FAMILY) SET Calculation_Strategy_Price_Tbl.OFF_INVOICE_REBATE_OFFSET = [Strategy_Off_Invoice_Rebate_Tbl].[REBATE_VALUE]
WHERE (((Strategy_Off_Invoice_Rebate_Tbl.CURRENCY)="USD"));

 

[ranman256]

i have never seen a query run itself over and over.
Executing a query will run it once for all N records.
Are you running it manually or in a macro or in VB?

 

[Joe4]

My main table has 126007 but when I try to update, the query is updating 997097

That probably means that your query has a one-to-many or many-to-many join between your tables.

If you temporarily change it to a Select query, this become pretty evident.

 

[Mac1206]

It's actually a one-to-one join, only include rows were the join fields are equal...

 

[Joe4]

Just to clarify...

Only including records where Join fields are equal does not necessarily make it a one-to-one join.
If the fields that you are joining on are all unique (guaranteed not to have duplicates), then that would guarantee a one-to-one join.

Run this select query, and see how many records it returns:

SELECT Calculation_Strategy_Price_Tbl.*
FROM Calculation_Strategy_Price_Tbl 
INNER JOIN Strategy_Off_Invoice_Rebate_Tbl 
ON (Calculation_Strategy_Price_Tbl.CUSTOMER_SOLD_TO_NUMBER = Strategy_Off_Invoice_Rebate_Tbl.CUSTOMER_CODE) 
AND (Calculation_Strategy_Price_Tbl.PRODUCT_FAMILY = Strategy_Off_Invoice_Rebate_Tbl.PRODUCT_FAMILY)
WHERE (((Strategy_Off_Invoice_Rebate_Tbl.CURRENCY)="USD"));

 

[Mac1206]

Just to clarify...

Only including records where Join fields are equal does not necessarily make it a one-to-one join.
If the fields that you are joining on are all unique (guaranteed not to have duplicates), then that would guarantee a one-to-one join.

Run this select query, and see how many records it returns:

SELECT Calculation_Strategy_Price_Tbl.*
FROM Calculation_Strategy_Price_Tbl 
INNER JOIN Strategy_Off_Invoice_Rebate_Tbl 
ON (Calculation_Strategy_Price_Tbl.CUSTOMER_SOLD_TO_NUMBER = Strategy_Off_Invoice_Rebate_Tbl.CUSTOMER_CODE) 
AND (Calculation_Strategy_Price_Tbl.PRODUCT_FAMILY = Strategy_Off_Invoice_Rebate_Tbl.PRODUCT_FAMILY)
WHERE (((Strategy_Off_Invoice_Rebate_Tbl.CURRENCY)="USD"));

It returned 997097 records...

 

[Joe4]

Yep, that proves that the relationship you have set up in your query between your tables is either one-to-many or many-to-many.
Note that my query is the same as yours, except it is a Select query and not an Update query (so it returns the exact same records your does).
So you need to fix your join to make it a one-to-one. Perhaps there is another field that is needed in your join?

You can easily identify where your "duplicate" join fields are occurring by doing an aggregate query on the join fields in each table, and seeing where two or more of the same combination exist.
The code to identify those would look like this:

SELECT Calculation_Strategy_Price_Tbl.CUSTOMER_SOLD_TO_NUMBER, Calculation_Strategy_Price_Tbl.PRODUCT_FAMILY, Count(Calculation_Strategy_Price_Tbl.PRODUCT_FAMILY) AS TotalCount
FROM Calculation_Strategy_Price_Tbl
GROUP BY Calculation_Strategy_Price_Tbl.CUSTOMER_SOLD_TO_NUMBER, Calculation_Strategy_Price_Tbl.PRODUCT_FAMILY 
HAVING (((Count(Calculation_Strategy_Price_Tbl.CUSTOMER_SOLD_TO_NUMBER))>1));

SELECT Strategy_Off_Invoice_Rebate_Tbl.CUSTOMER_CODE, Strategy_Off_Invoice_Rebate_Tbl.PRODUCT_FAMILY, Count(Strategy_Off_Invoice_Rebate_Tbl.CUSTOMER_CODE) AS TotalCount
FROM Strategy_Off_Invoice_Rebate_Tbl
GROUP BY Strategy_Off_Invoice_Rebate_Tbl.CUSTOMER_CODE, Strategy_Off_Invoice_Rebate_Tbl.PRODUCT_FAMILY
HAVING (((Count(Strategy_Off_Invoice_Rebate_Tbl.CUSTOMER_CODE))>1));

 

[Mac1206]

Hi Joe,

I only need it to update the 127007 records but it constantly returns 997097 records for some odd unexplained reason....

 

[Joe4]

I only need it to update the 127007 records but it constantly returns 997097 records for some odd unexplained reason....

No, there is a very good reason. I have tried to explain it. There is a problem with your query. It is not linking unique record combinations to unique record combinations. You have duplicates in there.

If you follow the steps I outlined in my last post (specifically, if you run those last two queries), it will show you exactly where your duplicates exists.

When trying to create Update Queries like this, it is best to start of with a Select query, to make sure you have it set up correctly and is returning the correct number of records. Once you have that part working correctly, then you can change it to an Update Query at that point.