访问 - 比较两个表并在第一个表中更新或插入数据 [英] Access - compare two tables and update or insert data in first table

问题描述

在我的 Access 数据库中，我有两个表:

In my Access datebase I have a two tables:

表 1:

    PersNum Name    Surname
    2321    Lenora  Springer
    2320    Donya   Gugino
    3326    Leland  Wittmer
    4588    Elmer   Mcdill

表 2:

    PersNum Name    Surname
    2321    Lenora  Farney
    2320    Donya   Willimas
    3326    Leland  Wittmer
    4588    Maya    Mcdill
    7785    Yolanda Southall
    1477    Hailey  Pinner 

我需要找到一种方法来检查个人号码(字段PersNum")，然后如果PersNum存在，则更新Table1中的Name和Surname.如果 PersNum 不存在，则在 Table1 中插入新行.

I need to find a way to check the personal number (field "PersNum"), and then if PersNum exist, update Name and Surname in Table1. If PersNum doesn't exist, insert new row in Table1.

预期结果:

    PersNum Name    Surname
    2321    Lenora  Farney      (updated surname)
    2320    Donya   Willimas    (updated surname)
    3326    Leland  Wittmer     (without change)
    4588    Maya    Mcdill      (without change)
    7785    Yolanda Southall    (new person)
    1477    Hailey  Pinner      (new person)

我正在寻找任何基于 SQL/VBA/DAO/ADO 的解决方案.

I'm looking for any solutions based on SQL/VBA/DAO/ADO.

推荐答案

一个选项是upsert"或组合的追加/更新查询.

One option is an "upsert" or combined append/update query.

这个来自 Smart Access 的旧提示是我最喜欢的提示之一:

This old tip from Smart Access is one of my favourites:

通过一个查询更新和追加记录

Update and Append Records with One Query

作者:艾伦·比格斯

您是否知道可以在 Access 中使用更新查询同时更新和添加记录?如果您有，这很有用两个版本的表，tblOld 和 tblNew，并且您想要集成从 tblNew 到 tblOld 的变化.

Did you know that you can use an update query in Access to both update and add records at the same time? This is useful if you have two versions of a table, tblOld and tblNew, and you want to integrate the changes from tblNew into tblOld.

请按照以下步骤操作:

1. 创建更新查询并添加两个表.通过将 tblNew 的关键字段拖到 tblOld 的匹配字段上来连接两个表.

1. Create an update query and add the two tables. Join the two tables by dragging the key field of tblNew onto the matching field of tblOld.

双击关系并选择包含来自 tblNew 的所有记录以及仅与来自 tblNew 的记录匹配的连接选项旧的.

Double-click on the relationship and choose the join option that includes all records from tblNew and only those that match from tblOld.

从 tblOld 中选择所有字段并将它们拖到 QBE 网格上.

Select all the fields from tblOld and drag them onto the QBE grid.

对于每个字段，在更新到单元格类型中的 tblNew.FieldName，其中 FieldName 匹配 tblOld 的字段名称.

For each field, in the Update To cell type in tblNew.FieldName, where FieldName matches the field name of tblOld.

从视图"菜单中选择查询属性"并将唯一记录"更改为假".(这会关闭 SQL 中的 DISTINCTROW 选项看法.如果您保留此选项，您将只获得一个空白记录结果，但您希望为每条新记录添加一个空白记录到 tblOld.)

Select Query Properties from the View menu and change Unique Records to False. (This switches off the DISTINCTROW option in the SQL view. If you leave this on you'll get only one blank record in your results, but you want one blank record for each new record to be added to tblOld.)

运行查询，您将看到对 tblNew 的更改现在位于 tblOld 中.

Run the query and you'll see the changes to tblNew are now in tblOld.

这只会将已添加到 tblNew 的记录添加到 tblOld.tblOld 中不存在于 tblNew 中的记录仍将保留在tblOld.

This will only add records to tblOld that have been added to tblNew. Records in tblOld that aren't present in tblNew will still remain in tblOld.

这篇关于访问 - 比较两个表并在第一个表中更新或插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！