访问-比较两个表并在第一个表中更新或插入数据 [英] 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，请更新表1中的姓名"和姓氏".如果PersNum不存在，请在表1中插入新行.

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

艾伦·比格斯(Alan Biggs)

By Alan Biggs

您是否知道可以在访问两者"中使用更新查询 同时更新和添加记录?如果您有的话，这很有用 表格的两个版本，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中的记录匹配的记录. tblOld.

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.

从视图"菜单中选择查询属性"，然后将唯一记录"更改为False. (这会关闭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. tblNew中不存在的tblOld中的记录仍将保留在 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屋！