Results 1 to 9 of 9
  1. #1
    droop is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    4

    Same field getting updated multiple times

    Hello,



    I am new to access. I will simplify my question to a smaller table.

    In Table1 a field needs to be updated if the associating fields are also found in another table. In Table1 there is one line for

    USER_ID, TYPE, IN_SCOPE.

    I need to update the IN_SCOPE field to "Yes" if that USER_ID and TYPE field is found in the Table2. The issue that I am having is that the field is getting updated more than once. I believe that this is caused by the USER_ID and TYPE match being in table 2 more than once.

    How can I avoid this from updating more than one time?

    Thanks for the help.

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Still not clear on what you are asking. If there are 2 records in Table2 with the same UserID, Type and Scope, how do you know which one should update Table1? Sounds like you have 2 tables with the same information. Why do you have to have Table1?

  3. #3
    droop is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    4
    Table 2 contains records for the USER_ID and TYPE. Say there's another field on Table2 there might be one row that says he has 6 cats, and another row that says 7 dogs. Table1 has a list of all the USER_IDs and TYPEs. Table1 is already populated. If the same USER_ID and TYPE match is in Table2. I need to update the in scope field in table1.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sounds like you have 2 tables with the same information. There is nothing unusual about this because it's often how tables are related (e.g. PO_Number showing in tblPO as well as PO_Notes).
    Not sure what you mean by
    The issue that I am having is that the field is getting updated more than once.
    If you mean more than one record is getting updated, that is one thing and should be described that way. The alternative is that the field in A record gets updated, then that field in that record gets updated again. Not sure how you'd know that unless you were stepping through code, which leads me to think it is the former.

    Make a select query that returns a single record if that's what you want. Then turn that into an update query. Your issue is likely that more than one record satisfies the criteria, thus more than one gets updated. Try putting more criteria into it to restrict the records, or change the join type.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    droop is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    4
    The same record is getting updated over an over again. The information stays constant it isnt changing values. I am just flagging if that ID and TYPE match is in the other table.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Then perhaps you need to post either small tables of data sets and the query sql or zip a copy of your db and post it here. But be very clear on what you want because I'm not sure. Once you've updated a field in a record based on criteria, unless the criteria or the value to use in the update changes (e.g. from True to False) then I don't know what you're expecting once the update occurs.

  7. #7
    droop is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    4
    The same record is getting updated over an over again. The information stays constant it isnt changing values. I am just flagging if that ID and TYPE match is in the other table.

  8. #8
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Quote Originally Posted by droop View Post
    I need to update the IN_SCOPE field to "Yes" if that USER_ID and TYPE field is found in the Table2.
    Does it matter that it is getting updated more than once to Yes? If so:

    Solution1: Instead of using Table2, as Micron said use a query with just UserID and Type and Group on both fields so you get 1 unique list. Then match that to Table1 to get your matches.
    Solution2: User a Dlookup function to see if there is a record in Table2 with UserID and Type, if there is mark the field in Table 1 with Yes.
    vMatch = DLookup("[UserID]", "Table2", "UserID = " & Table1.UserID & " AND Type = " & Table1.Type)
    If not Isnull(vMatch) then Table1.IN_SCOPE = "Yes" Else Table1.IN_SCOPE = "No"

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It's possible that the whole exercise is moot. Why flag a record in this manner when a query returns the answer? What is then done if the record being looked up is updated or deleted? Seems to me that this is like storing a calculation, which is almost never done.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 03-12-2016, 02:06 PM
  2. Replies: 4
    Last Post: 04-06-2014, 10:01 AM
  3. Replies: 2
    Last Post: 12-31-2013, 03:41 PM
  4. Replies: 34
    Last Post: 09-20-2012, 07:02 AM
  5. Replies: 5
    Last Post: 12-10-2009, 10:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums