Update Query from a Lookup Query

Question

I have a spreadsheet that I am converting to an Access DB. I have a column of typed out customer names that I want to replace with the appropriate customer number from our accounting system.

I have created a table with the customer info, and a query that shows what ID needs to be inserted into the source data. What I'm looking for is:

UPDATE tblStarting_Data
SET CustomerID=x
WHERE TEMPCustomer=y

Where X and Y come from qryIDPerCustomer.

Can I use a loop? How do I reference another query?

Efface

Answer 1

Another possibility in MS Access (object names borrowed from Tomalak answer):

UPDATE tblStarting_Data, qryIDPerCustomer
SET tblStarting_Data.CustomerID=qryIDPerCustomer.CustomerID
WHERE tblStarting_Data.TEMPCustomer=qryIDPerCustomer.CustomerName

Rubyeruch

+1 -- I was unsure if Access supported updating a joined query (a few years ago I would have known by heart... sigh). I had to fire it up and try it out. – Jonellejones

I think it doesn't support usual T-SQL construct "update x set a=y.b from x join y" and it doesn't support joining with aggregate query, other variants usually work. But I've no MS Access here to check out exact rules; so haven't I written Access queris for year or two :) – Rubyeruch

I was a bit unclear with the request. Your code ends up being UPDATE tblStarting_Data, qryIDPerCustomer SET tblStarting_Data.CustomerID=qryIDPerCustomer.CustomerID WHERE tblStarting_Data.OpportunityID=qryIDPerCustomer.OpportunityID You got me close enough to what I needed though, THANKS! – Efface

Answer 2

I think a JOIN will help you:

UPDATE 
  tblStarting_Data AS sd 
  INNER JOIN qryIDPerCustomer AS qc ON sd.TEMPCustomer = qc.CustomerName
SET 
  sd.CustomerID = qc.CustomerID;

This can be expressed as a correlated sub-query as well (though the join syntax is preferable):

UPDATE 
  tblStarting_Data
SET 
  CustomerID = (
    SELECT  CustomerID 
    FROM    qryIDPerCustomer
    WHERE   CustomerName = tblStarting_Data.TEMPCustomer
  )

No need for a loop, both statements will update all records in tblStarting_Data in one step.

Jonellejones

I couldn't get your method to work... It makes sense when I read it, but in code it just won't execute. – Efface

Your correlated sub-query syntax, despite being valid Standard SQL-92 syntax, is not supported by ACE/Jet (even in its own ANSI-92 Query Mode!) – Windsor