Results 1 to 5 of 5
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305

    Use a query to update a table


    I've got a table full of customers and a smaller query also filled with customers. In both are three fields each: customerID, customerFirstName, and customerLastName.

    I'm trying to create an UPDATE query where, based on the customerID, the query updates any changes to the customerFirstName fields in the table. Anyone know how the SQL should be set up? I'm struggling

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Open the query designer and add both the table and the query, join them by CustomerID and make the query an Update query (by clicking on the ribbon).
    Should look something like this (adapt to match your table and query names):

    UPDATE TblCustomer INNER JOIN qryCustomer ON TblCustomer.CustomerID = qryCustomer.CustomerID SET TblCustomer.customerFirstName = [qryCustomer]![customerFirstName], TblCustomer.customerLastName = [qryCustomer]![customerLastName];

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Quote Originally Posted by Gicu View Post
    Open the query designer and add both the table and the query, join them by CustomerID and make the query an Update query (by clicking on the ribbon).
    Should look something like this (adapt to match your table and query names):

    UPDATE TblCustomer INNER JOIN qryCustomer ON TblCustomer.CustomerID = qryCustomer.CustomerID SET TblCustomer.customerFirstName = [qryCustomer]![customerFirstName], TblCustomer.customerLastName = [qryCustomer]![customerLastName];

    Cheers,
    So I used the above code but I'm getting an error that states "operation must use an updateable query" Any ideas? Below is my exact code:

    Code:
    UPDATE tableCustomers INNER JOIN qryFix ON [tableCustomers].CustomerID = qryFix.CustomerID SET [tableCustomers].CustomerLastName = [qryFix]![CustomerLastName];

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I was afraid you were going to come back with that . Usually when you get this error you will need to use a temporary table. So you change the query (qryFix) into a make table query to create a new tempFix table (or even better an append query as that allows you to preserve the unique index on CustomerID in the temporary table-you run a delete query first to empty the tempFix then run the append).

    Here is some reading for you:
    https://www.fmsinc.com/microsoftacce...ble/index.html
    http://rogersaccessblog.blogspot.com...eable-why.html

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 10
    Last Post: 07-14-2021, 02:08 PM
  2. Update Query with Criteria in update to table
    By tanner.morgan in forum Queries
    Replies: 3
    Last Post: 08-28-2018, 07:56 AM
  3. Replies: 4
    Last Post: 04-27-2016, 04:36 PM
  4. Replies: 1
    Last Post: 05-17-2013, 12:53 PM
  5. Replies: 1
    Last Post: 02-08-2012, 04:50 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