如何消除 MS Access 2003 中具有唯一 ID 的重复条目？

发布于 2024-08-31 13:01:47 字数 440 浏览 10 评论 0原文

我正在开发一个包含大量重复条目的 MS Access 数据库。问题是有一个学生表，有时有人会用不同的 ID 再次添加该学生，而不是仅仅更新某个学生的信息。我想删除所有重复项（这很痛苦，因为几乎没有任何方法可以区分它们），只需删除重复项就可以了，但其他表可能依赖于重复项。如何将依赖某个 ID 的所有表更改为依赖我选择保留的 ID？

它看起来是这样的：

 Student ID | L. Name |F. Name

 ANDY-01    | Andy    |  Andy

 ANDY-02    | Andy    |  Andy

然后在课程表中，我有 ANDY-01 会修读的课程，以及 ANDY-02 会修读的课程。我想将所有表中包含 ANDY-01 和 ANDY-02 的所有条目合并为 ANDY-01。我该怎么办呢？

（不用担心我如何区分 ANDY-01 和 ANDY-02）

原文

I'm working on an MS Access Database with tons of duplicate entries. The problem is that there is a table of students, and sometimes instead of just updating a certain student's information, someone would just add the student in again with a different ID. I want to get rid of all the duplicates (which is a pain since there's barely any way to differentiate them), which would be fine by just deleting the duplicates, except that other tables may rely on the duplicate. How can I change all the tables that rely on a certain ID to rely on the ID that I choose to keep?

Here's what it looks like:

 Student ID | L. Name |F. Name

 ANDY-01    | Andy    |  Andy

 ANDY-02    | Andy    |  Andy

Then in the courses table I'd have courses that ANDY-01 would have taken, and courses ANDY-02 would have taken. I want to merge all entries in all the tables that would have ANDY-01 and ANDY-02 as ANDY-01. How would I go about this?

(Don't worry about how I'm differentiating between ANDY-01 and ANDY-02)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

舂唻埖巳落 2024-09-07 13:01:47

Riho的回答+1。要更新多个表，您可以创建如下所示的过程，然后手动更新 ID 值并为每个学生执行该过程。
如果您有一个映射新旧 ID 的表或查询，您可以编写另一个过程来读取该表并为每个学生调用此过程。

Public Sub UpdateStudent()
    Dim oldID As String
    Dim newID As String

    oldID = "ID1"
    newID = "ID2"

    DoCmd.Execute "update another_table set student_id='" & newID & "' where student_id=" & oldID
    DoCmd.Execute "update yet_another_table set student_id='" & newID & "' where student_id=" & oldID
End Sub

+1 for Riho's answer. To update multiple tables you could create a procedure like the one below, and manually update the ID values and execute the procedure for each student.
If you have a table or query that maps the old and new IDs you could write another procedure to read the table and call this procedure for each student.

Public Sub UpdateStudent()
    Dim oldID As String
    Dim newID As String

    oldID = "ID1"
    newID = "ID2"

    DoCmd.Execute "update another_table set student_id='" & newID & "' where student_id=" & oldID
    DoCmd.Execute "update yet_another_table set student_id='" & newID & "' where student_id=" & oldID
End Sub

回复收藏 0 原文