从查找查询更新查询

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.

我有一个电子表格,我正在转换为Access数据库。我有一列输入的客户名称,我想用我们的会计系统中的相应客户编号替换。

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:

我创建了一个包含客户信息的表,以及一个显示需要将哪些ID插入源数据的查询。我正在寻找的是:

UPDATE tblStarting_Data
SET CustomerID=x
WHERE TEMPCustomer=y

Where X and Y come from qryIDPerCustomer.

其中X和Y来自qryIDPerCustomer。

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

我可以使用循环吗?如何引用其他查询?

2 个解决方案

#1

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

MS Access中的另一种可能性(从Tomalak借来的对象名称答案):

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

#2

I think a JOIN will help you:

我认为JOIN会帮助你:

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.

不需要循环,两个语句将一步更新tblStarting_Data中的所有记录。

#1