Results 1 to 4 of 4
  1. #1
    Darla is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    May 2016
    Posts
    25

    using a lookup in an update query across 3 tables?

    Hi everyone. I'm pretty new to access and I was tasked with making a query that updates a table in access. So basically I have 3 tables, and I have to match a field in the first table to a field in the second table, then match 2 other fields for those records to a third table and update the first with that value. My head is spinning from this and I'm confusing myself!!



    So this is what I need to do:

    Take field
    Customer_ID from tblCustomer
    Match
    Customer_ID value to field SNumber in table tblConvert
    There will be duplicate values for
    SNumber in tblConvert, but there are other fields for each record that are unique, Property and CustTag
    Take the records that matched
    SNumber and use the fields Property and CustTag for those records and look Property and CustTag up in a table called tblID and return the field called SCODE that matched Property and CustTag and then update LastName in tblCustomer with the SCODE value


    Is an update query even the way to go?
    tblConvert and tblID are linked tables that reside on a SQL server that cannot be modified, so I was just guessing that this was the best way to do it? Is this even possible or should I just take a xanax and go home?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    yes, update query IS the way to update tables.
    If you don't want duplicates, then you need to make another query that pulls the unique records you want and add this query to your main query.
    you may have to make sub queries to get what you need, no need to put it all in a single query.

  3. #3
    Darla is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    May 2016
    Posts
    25
    So should I make some temporary tables or something to do this? I'm not sure how to nest all these queries together so they all run at once.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You have a problem there. tblCustomer can match more than one record in tblConvert. OK, but now each of those records in tblConvert is used to get an SCODE value which will update a field in tblCustomer. The problem is that because you have a 1-to-many relationship, both values of SCODE are going to be trying to update the same record in tblCustomer (because tblCustomer is on the "1" side of the relationship), which won't work (and doesn't make sense).

    So, you are going to have to rethink what it is you are trying to do.

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

Similar Threads

  1. why query not update tables
    By playtime38 in forum Queries
    Replies: 4
    Last Post: 12-19-2015, 10:59 PM
  2. Update query to set null value lookup field
    By alexjose in forum Access
    Replies: 1
    Last Post: 06-24-2013, 04:26 PM
  3. update query using a lookup table
    By slimjen in forum Queries
    Replies: 2
    Last Post: 04-27-2012, 03:46 PM
  4. Update 2 tables with one Update Query
    By Douglasrac in forum Queries
    Replies: 4
    Last Post: 04-14-2011, 08:33 AM
  5. Replies: 1
    Last Post: 07-06-2007, 08:27 AM

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