每天根据日期删除重复记录 [英] Delete duplicate records daily based on Date

问题描述

我每天都会将名称上传到数据库中，有时会给我以前提交过的重复名称或名称。基本上我想删除除日期以外的所有最新记录。我看过很多关于这个的帖子，但我还没有真正得到任何工作。在我的桌子上[入站]我有[NAME]，[SSN]，[RANK]，[ULN]和[DATE]字段。我想删除具有相同SSN的所有重复记录，不包括Null值。我想删除旧日期的记录。我理解删除查询的最小/最大设置，但我无法使其正常工作。我想我离开了，所以我重新开始。下面是我阅读的一个页面，其中包含有关该问题的一些详细信息，但我不了解访问权限以获得我需要的内容。

http://bytes.com/topic /access/answer...licate-records


NeoPa，


我认为你的帖子可能有最接近的答案但是我不知道该去哪里。


Sgt B

解决方案

以下示例使用两个子查询，一个用于匹配SSN和另一个以排除包含该SSN的最大日期的行。请将示例中的表名''tblTest'替换为相关表的真实名称。

展开 | < span class =codeLinkonclick =selectAll（this）;>选择 | Wrap | 行号

斯图尔特，

一段很棒的代码！我永远无法在Access中使用这些相关的子查询内容，所以做得很好！


Stevan，

我个人不喜欢删除，很快就不会添加首先创建副本的数据。我会尽快循环通过INCOMING，如果SSN已存在于主数据中，则执行UPDATE命令，否则执行APPEND。


我也猜测可能存在与包含[名称]，[SSN]，[等级]的表相关联的从属（相关）记录...如果您通过[SSN]链接然后确定，但美国SSN是九位数和两个破折号，即一个11个字符的字符串，而不是我选择作为链接到其他表的关键字段。如果它们是通过Long Integer（自动编号）字段链接的，那么当删除早期记录时，从属数据将成为孤立状态。


但也许其他人只是过着简单的生活！ />
S7

一般来说，我会同意S7的说法：

" 我个人不喜欢删除，很快就不会添加首先创建副本的数据。我会更快地循环通过INCOMING并且如果SSN已经存在于主数据中然后执行UPDATE命令，否则执行APPEND。"


在您的情况下，从整体数据中删除的项目可以来自两个来源（以前的好数据和新的传入数据），然后我会倾向于首先进行导入，然后在一个表中整理。 />

下面的SQL，与斯图尔特的想法非常类似，应该为你做的诀窍：

展开 < span class =codeDivider> | 选择 | Wrap | Line编号

I upload names into a database everyday and sometimes I am given duplicate names or names that have previously been submitted. Basically I want to delete all but the latest record based off of the date. I have seen many post about this but I haven''t really been able to get anything to work. On my table [Inbound] I have fields [NAME], [SSN], [RANK], [ULN], and [DATE]. I want to delete all duplicate records that have the same SSN excluding Null values. The catch is I want to delete records with the older date. I understand the Min/Max setting on a delete query but I am having trouble getting it to work properly. I figure I am way off so I am starting over. Below is a page that I read with some details regarding the issue but I don''t understand access enough to get what I need out of it.

http://bytes.com/topic/access/answer...licate-records

NeoPa,

I think your post might have the closest answer but I am not sure where to go with it.

Sgt B

解决方案

The example below uses two subqueries, one to match the SSN and the other to exclude rows containing the maximum date for that SSN. Please replace the table name ''tblTest'' in the example with the real name of the table concerned.

Expand|Select|Wrap|Line Numbers

Stewart,
A nice piece of code! I could never get this correlated sub-query stuff to work in Access, so well done!

Stevan,
Personally I hate deleting and would sooner not add the data to create a duplicate in the first place. I would sooner loop through the INCOMING and IF the SSN already existed in the main data then do an UPDATE command, else do the APPEND.

I''m also surmising that there may be subordinate (related) records associated with the table that holds [Name],[SSN],[Rank]... If you link via [SSN] then ok but a US SSN is nine digits and two dashes, i.e. an 11 character string, not what I would choose as a key field to link to other tables. If they were linked via a Long Integer (autonumber) field then the subordinated data would be orphaned when the earlier record was deleted.

But perhaps other people just have a simple life!
S7

Generally I would agree with S7 when they say :
"Personally I hate deleting and would sooner not add the data to create a duplicate in the first place. I would sooner loop through the INCOMING and IF the SSN already existed in the main data then do an UPDATE command, else do the APPEND."

In your situation though, where items to be removed from the overall data can come from both sources (previously good data as well as new incoming data), then I''d be inclined to do the import first and then tidy up in a single table.

The following SQL, which is on very similar lines to Stewart''s mind you, should do the trick for you :

Expand|Select|Wrap|Line Numbers

这篇关于每天根据日期删除重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！