访问查询或 VBA 代码以根据第一行唯一标识符将数据从一个表移动到另一个表

Access query or VBA code to move data from one table to another depending on first row unique identifier

提问by Tracy C

Is it possible to write a query to loop through the rows of a two column table, checking the first column for a certain identifier and copy the data in the second column into a new table?

是否可以编写一个查询来遍历两列表的行，检查第一列是否有某个标识符并将第二列中的数据复制到新表中？

Example:

例子：

tblSurveyData
FirstColumn     Second Column
A0              John
A2              Smith
A3              05-01-1973

tblSurveyReport
FirstName   MiddleName  LastName    DateOfBirth 
John                    Smith       05-01-1973

Where A0 data would go to FirstName, A1 MiddleName, A2 LastName and A3 DateOfBirth. There are many more identifiers and fields but just as an example how would you do this with a query in Access or is VBA a better solution?

A0 数据将转到 FirstName、A1 MiddleName、A2 LastName 和 A3 DateOfBirth。还有更多的标识符和字段，但举个例子，您将如何使用 Access 中的查询来执行此操作，或者 VBA 是更好的解决方案吗？

The only solution I came up with is the following VBA but this bypasses the two column table and tries to insert into the tblSurveyReport table. Unfortunately, it puts each piece of data into its own row which doesn't help.

我想出的唯一解决方案是以下 VBA，但这绕过了两列表并尝试插入到 tblSurveyReport 表中。不幸的是，它将每条数据放入自己的行中，这无济于事。

If Identifier = "A0" Then
DoCmd.RunSQL "INSERT INTO tblSurveyReport " _
            & "(FirstName) " _
            & "VALUES ('" & Info & "')"
ElseIf Identifier = "A1" Then
DoCmd.RunSQL "INSERT INTO tblSurveyReport " _
            & "(MiddleName) " _
            & "VALUES ('" & Info & "')"
ElseIf Identifier = "A2" Then
DoCmd.RunSQL "INSERT INTO tblSurveyReport " _
            & "(LastName) " _
            & "VALUES ('" & Info & "')"
ElseIf Identifier = "A3" Then
DoCmd.RunSQL "INSERT INTO tblSurveyReport " _
            & "(DateOfBirth) " _
            & "VALUES ('" & Info & "')"
End If

However each piece of data is going into its own row and I need it all in the same row.

然而，每条数据都进入自己的行，我需要在同一行中。

Any help is greatly appreciated.

任何帮助是极大的赞赏。

Thanks in advance.

提前致谢。

TC

技术委员会

采纳答案by Shawn E

Use INSERT INTOwith a SELECT statement

将INSERT INTO与 SELECT 语句一起使用

INSERT INTO tblSurveyReport(FirstName) SELECT FirstName FROM tblSurveyData where FirstColumn = 'A0'

INSERT INTO tblSurveyReport(MiddleName) SELECT MiddleName FROM tblSurveyData where FirstColumn = 'A1'

You could run this using a DoCmd, as a query in Access, etc.

您可以使用 DoCmd 运行它，作为 Access 中的查询等。

回答by Johnny Bones

You will need something that would link your records together. What happens if the data gets re-sorted? How would you know that all the info in your example should be in the same record? I believe the only way to do something like this would be to create a 3rd field in your first table that determines which data belongs with which, something like a UserID. Then the table would look like this:

您将需要将您的记录链接在一起的东西。如果数据被重新排序会发生什么？你怎么知道你的例子中的所有信息都应该在同一个记录中？我相信这样做的唯一方法是在您的第一个表中创建第三个字段，以确定哪些数据属于哪个，例如 UserID。然后表格看起来像这样：

tblSurveyData

tblSurveyData

FirstColumn     Second Column      UserID
A0              John               XX001
A2              Smith              XX001
A3              05-01-1973         XX001

Then you could create a preliminary query like:

然后您可以创建一个初步查询，如：

Select DISTINCT UserID from tblSurveyData

Use that as your "pointer" query and loop through the results, and then you can pull all the records with each UserID out and copy them into the new table. Or, you can inner join the "pointer" query and tblSurveyData, and then use a Crosstab query. The easiest way to do that would be to use the wizard to create it, and then just copy the code into your VBA.

使用它作为您的“指针”查询并遍历结果，然后您可以拉出每个用户 ID 的所有记录并将它们复制到新表中。或者，您可以内部加入“指针”查询和 tblSurveyData，然后使用交叉表查询。最简单的方法是使用向导创建它，然后将代码复制到 VBA 中。

EDIT:For easier readability for future readers, the SQL for the query you're asking for in your comment is:

编辑：为了让未来的读者更容易阅读，您在评论中要求的查询的 SQL 是：

SELECT Max(IIf([Identifier]="A0",[IValue],"")) AS FName, Max(IIf([Identifier]="A1",[IValue],"")) AS MName, Max(IIf([Identifier]="A2",[IValue],"")) AS LName, Max(IIf([Identifier]="A3",[IValue],"")) AS BDate FROM tblSurveyData; 

You will need to change "First Column" in your sample data to "Identifier", and "Second Column" to "IValue" (or make the corresponding field name changes to the above SQL). I have tested this and it worked perfectly, giving you one record with all 4 values in corresponding fields. Once you've pasted that into a blank query, you can switch to Design View and change the query to an Append or MakeTable query and that will let you export the results to tblSurveyReport.

您需要将示例数据中的“第一列”更改为“标识符”，将“第二列”更改为“IValue”（或将相应的字段名称更改为上述 SQL）。我已经对此进行了测试，并且效果很好，为您提供了一个记录，其中包含相应字段中的所有 4 个值。将其粘贴到空白查询后，您可以切换到设计视图并将查询更改为 Append 或 MakeTable 查询，这样您就可以将结果导出到 tblSurveyReport。

回答by Philippe Grondier

You could have simply two recordsets, like this:

您可以只拥有两个记录集，如下所示：

• 1st recordset: rsSurveyData
• 2nd recordset: rsSurveyReport

• 第一个记录集：rsSurveyData
• 第二个记录集：rsSurveyReport

The idea is to browse the first recordset along its records, and the second along its fields.

这个想法是沿着它的记录浏览第一个记录集，沿着它的字段浏览第二个记录集。

depending on the recordset object you are using (DAO or ADODB), the opening syntax will be slightly different, but you'll find all details in the help. I am using here the ADODB syntax for the 'Find', 'Move', and 'Update' methods. DAO recordsets need an extra 'Edit' method before changing the data. And I do not remember if the fields collection of the recordset object is indexed from 0 or 1 ...

根据您使用的记录集对象（DAO 或 ADODB），打开语法会略有不同，但您可以在帮助中找到所有详细信息。我在这里使用了“查找”、“移动”和“更新”方法的 ADODB 语法。在更改数据之前，DAO 记录集需要一个额外的“编辑”方法。而且我不记得记录集对象的字段集合是从 0 还是 1 索引的...

Then:

然后：

rsSurveyData.moveFirst
rsSurveyReport.Find "FirstName ='" & rsSurveyData.fields("A0") & "'"
if rsSurveyReport.EOF then
    'do what you have to do when the record does not exist, for example:'
    rsSurveyReport.addNew
    rsSurveyreport.fields(1) = rsData.fields(1)
Else
    'you''ve just found the record that needs to be updated'
    for i = 2 to rsSurveyData.recordcount
        rsSurveyData.move i, 1    
        rsSurveyReport.fields(i)=rsSurveyData.fields(1) 
    next i
    rsSurveyReport.update
Endif