Results 1 to 3 of 3
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Update Top 1 Record Only

    I have a master table that has multiple lines per userID and a prod table which only has one row per userID. The prod table is just "prettied up" data that is output for reporting, and it holds a field called sumalogN which matches to the field sumalogN in the master table. I want to update the prod table with the data from the master table.

    Sample data structure is like this
    master
    userid sumalogN


    1 abc
    1 abc
    1 abc
    1 abc
    2 def
    2 def


    What would be the syntax for an update query to join on userID and update sumalogN in the prod table with the matching value from the master table?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Thread title a bit misleading.

    I don't think this can be done with a nested query because pulling the TOP 1 for each userid group involves a nested subquery and then joining to prod might not be an updatable query. If you want to try, reference http://allenbrowne.com/subquery-01.html#TopN

    Why are there duplicate records in the example? Are there other fields that establish uniqueness for each record? You want the sumalogN value updated in prod for each userid?

    Consider:

    UPDATE prod SET sumalogN = DLookup("sumalogN", "master", "userid=" & userid)

    Why is prod table even needed? What does it accomplish that queries cannot?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    That dlookup example did it perfectly. Thank you for the help!

    And yes in the master table there are other fields that make each record unique, I was just excluding them for the examples sake.

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

Similar Threads

  1. Replies: 16
    Last Post: 01-18-2016, 07:25 PM
  2. Replies: 3
    Last Post: 10-04-2015, 10:17 AM
  3. Update Table after Record Update with Form
    By speciman_A in forum Forms
    Replies: 25
    Last Post: 10-31-2014, 01:00 PM
  4. Replies: 14
    Last Post: 08-12-2014, 06:33 AM
  5. Replies: 4
    Last Post: 12-14-2012, 06: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