访问：如果两个字段相同，则删除重复项(Access: Removing a duplicate if two fields are the same)

访问：如果两个字段相同，则删除重复项(Access: Removing a duplicate if two fields are the same)

我有一个Access数据库表，有时包含重复的ProfileID。 我想创建一个查询，排除重复记录中的一个（或更多，如果需要）。

要排除重复记录的条件是：如果PriceBefore和PriceAfter字段不相等，则认为它们是重复的。 如果它们相等，则保留重复字段。

在上面的示例表中，ID为7和8的记录具有相同的ProfileID。 对于ID 8，PriceBefore和PriceAfter 不相等，因此应从查询中排除此记录。 对于ID 7，两者是相等的，所以它仍然存在。 另请注意，ID 4的PriceBefore和PriceAfter是相同的，但由于ProfileID不是重复的，因此必须保留记录。

做这个的最好方式是什么？ 如有必要，我很乐意使用多个查询。

I have an Access database table which sometimes contains duplicate ProfileIDs. I would like to create a query that excludes one (or more, if necessary) of the duplicate records.

The condition for a duplicate record to be excluded is: if the PriceBefore and PriceAfter fields are NOT equal, they are considered duplicate. If they are equal, the duplicate field remains.

In the example table above, the records with ID 7 and 8 have the same ProfileIDs. For ID 8, PriceBefore and PriceAfter are not equal, so this record should be excluded from the query. For ID 7, the two are equal, so it remains. Also note that PriceBefore and PriceAfter for ID 4 are the same, but as the ProfileID is not a duplicate, the record must remain.

What is the best way to do this? I am happy to use multiple queries if necessary.

最满意答案

创建指针查询。 称之为pQuery：

SELECT ProfileID, Sum(1) as X FROM MyTableName HAVING Sum(1) > 1

这将为您提供每个记录的ProfileID。

接下来，找到价格不匹配的记录。 调用此pNoMatchQuery：

SELECT MyTableName.* FROM MyTableName INNER JOIN pQuery ON pQuery.ProfileID = MyTableName.ProfileID WHERE PriceBefore <> PriceAfter

您现在可以查询应从数据集中排除的每条记录。 如果要永久删除所有这些记录，请运行DELETE查询，将内部表连接到pNoMatchQuery：

Delete MyTableName.* From MyTableName Where Exists( Select 1 From pNoMatchQuery Where pNoMatchQuery.ID = MyTableName.ID ) = True

首先，确保pQuery和pNoMatchQuery在您从源表中删除任何内容之前返回您所期望的内容，因为一旦它消失，它就会消失（除非您首先进行备份，我强烈建议您在运行删除之前第一次）。

Create a pointer query. Call it pQuery:

SELECT ProfileID, Sum(1) as X FROM MyTableName HAVING Sum(1) > 1

This will give you the ProfileID of every record that's part of a dupe.

Next, find the records where prices don't match. Call this pNoMatchQuery:

SELECT MyTableName.* FROM MyTableName INNER JOIN pQuery ON pQuery.ProfileID = MyTableName.ProfileID WHERE PriceBefore <> PriceAfter

You now have a query of every record that should be excluded from your dataset. If you want to permanently delete all of these records, run a DELETE query where you inner join your source table to pNoMatchQuery:

Delete MyTableName.* From MyTableName Where Exists( Select 1 From pNoMatchQuery Where pNoMatchQuery.ID = MyTableName.ID ) = True

First, make absolutely sure that pQuery and pNoMatchQuery are returning what you expect before you delete anything from your source table, because once it's gone it's gone for good (unless you make a backup first, which I would highly suggest before you run that delete the first time).

访问：如果两个字段相同，则删除重复项(Access: Removing a duplicate if two fields are the same)

我有一个Access数据库表，有时包含重复的ProfileID。 我想创建一个查询，排除重复记录中的一个（或更多，如果需要）。

要排除重复记录的条件是：如果PriceBefore和PriceAfter字段不相等，则认为它们是重复的。 如果它们相等，则保留重复字段。

在上面的示例表中，ID为7和8的记录具有相同的ProfileID。 对于ID 8，PriceBefore和PriceAfter 不相等，因此应从查询中排除此记录。 对于ID 7，两者是相等的，所以它仍然存在。 另请注意，ID 4的PriceBefore和PriceAfter是相同的，但由于ProfileID不是重复的，因此必须保留记录。

做这个的最好方式是什么？ 如有必要，我很乐意使用多个查询。

I have an Access database table which sometimes contains duplicate ProfileIDs. I would like to create a query that excludes one (or more, if necessary) of the duplicate records.

The condition for a duplicate record to be excluded is: if the PriceBefore and PriceAfter fields are NOT equal, they are considered duplicate. If they are equal, the duplicate field remains.

In the example table above, the records with ID 7 and 8 have the same ProfileIDs. For ID 8, PriceBefore and PriceAfter are not equal, so this record should be excluded from the query. For ID 7, the two are equal, so it remains. Also note that PriceBefore and PriceAfter for ID 4 are the same, but as the ProfileID is not a duplicate, the record must remain.

What is the best way to do this? I am happy to use multiple queries if necessary.

最满意答案

创建指针查询。 称之为pQuery：

SELECT ProfileID, Sum(1) as X FROM MyTableName HAVING Sum(1) > 1

这将为您提供每个记录的ProfileID。

接下来，找到价格不匹配的记录。 调用此pNoMatchQuery：

SELECT MyTableName.* FROM MyTableName INNER JOIN pQuery ON pQuery.ProfileID = MyTableName.ProfileID WHERE PriceBefore <> PriceAfter

您现在可以查询应从数据集中排除的每条记录。 如果要永久删除所有这些记录，请运行DELETE查询，将内部表连接到pNoMatchQuery：

Delete MyTableName.* From MyTableName Where Exists( Select 1 From pNoMatchQuery Where pNoMatchQuery.ID = MyTableName.ID ) = True

首先，确保pQuery和pNoMatchQuery在您从源表中删除任何内容之前返回您所期望的内容，因为一旦它消失，它就会消失（除非您首先进行备份，我强烈建议您在运行删除之前第一次）。

Create a pointer query. Call it pQuery:

SELECT ProfileID, Sum(1) as X FROM MyTableName HAVING Sum(1) > 1

This will give you the ProfileID of every record that's part of a dupe.

Next, find the records where prices don't match. Call this pNoMatchQuery:

SELECT MyTableName.* FROM MyTableName INNER JOIN pQuery ON pQuery.ProfileID = MyTableName.ProfileID WHERE PriceBefore <> PriceAfter

You now have a query of every record that should be excluded from your dataset. If you want to permanently delete all of these records, run a DELETE query where you inner join your source table to pNoMatchQuery:

Delete MyTableName.* From MyTableName Where Exists( Select 1 From pNoMatchQuery Where pNoMatchQuery.ID = MyTableName.ID ) = True

First, make absolutely sure that pQuery and pNoMatchQuery are returning what you expect before you delete anything from your source table, because once it's gone it's gone for good (unless you make a backup first, which I would highly suggest before you run that delete the first time).