Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Access 2013 updating over and over again

  1. #1
    Board Regular
    Join Date
    Jun 2016
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Access 2013 updating over and over again

    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"));

  2. #2
    Board Regular ranman256's Avatar
    Join Date
    Jun 2014
    Location
    Kentucky
    Posts
    1,135
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access 2013 updating over and over again

    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?

  3. #3
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    40,866
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Access 2013 updating over and over again

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    Board Regular
    Join Date
    Jun 2016
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access 2013 updating over and over again

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

  5. #5
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    40,866
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Access 2013 updating over and over again

    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:
    Code:
    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"));
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    Board Regular
    Join Date
    Jun 2016
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access 2013 updating over and over again

    Quote Originally Posted by Joe4 View Post
    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:
    Code:
    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...

  7. #7
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    40,866
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Access 2013 updating over and over again

    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:

    Code:
    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));
    Code:
    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));
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    Board Regular
    Join Date
    Jun 2016
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access 2013 updating over and over again

    Hi Joe,

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

  9. #9
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    40,866
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Access 2013 updating over and over again

    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.
    Last edited by Joe4; Oct 11th, 2017 at 02:16 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com