选择查询以产生相同记录的重复结果(Access 2010) [英] Select Query producing duplicate results of the same records (Access 2010)

问题描述

我有一个选择查询，该查询是在查询设计工具的SQL视图中设计的.根据我的一些结果，我发现了相同记录的重复项.由于表中没有倍数，因此只有查询(相同的主键).这是原始查询.

I had a select query, which I designed in the SQL view of the query design tool. With some of my results I found duplicates of the same records. As in there were not multiples in the table, only the query (Same Primary Key). Here is the original query.

SELECT t1.* 
FROM Inventory AS t1 INNER JOIN Inventory AS t2 ON
t1.Part_ID = t2.Part_ID WHERE (t1.Inventory_ID<>t2.Inventory_ID);

我旨在查询Inventory以查找具有相同Part_ID(FK)但具有不同Inventory_ID(PK)的记录. part_ID(FK)和location_ID(FK)之间有一个复合键，如果有区别的话.

I aimed to query Inventory for records with the same Part_ID (FK) but different Inventory_ID(PK). There is a composite key between part_ID (FK) and location_ID (FK), if that makes any difference.

此后，我将此查询更改为:

I have since changed this query to:

SELECT DISTINCT t1.*
FROM Inventory AS t1 INNER JOIN Inventory AS t2 ON t1.Part_ID = t2.Part_ID
WHERE (t1.Inventory_ID<>t2.Inventory_ID);

这将删除重复的记录，但是，我不认为我的原始查询应该产生重复的数据结果.我担心这表明我的桌子有问题吗?

This removes the duplicate records, however, I don't believe that my original query should produce replicate data results. I am worried that this suggests that there is something wrong with my tables?

我的表如下所示:

谢谢

推荐答案

问题是，您可能在选择的INNER JOIN侧上多次出现part_ID.因此，如果在另外2个位置中存在具有相同part_ID和不同库存ID的零件，则将得到重复的零件.

The thing is that you might have multiple occurences of part_ID on the INNER JOIN side of your select. So if a part with the same part_ID and a different inventory_ID exists in 2 other locations, you will get duplicates.

要进行检查，可以对一些重复项进行测试，或者使用查询的INNER JOIN端的GROUP BY指令重写原始查询.

To check that, you could do a test on a few duplicates, or rewrite your original query with a GROUP BY instruction on the INNER JOIN side of the query.

这篇关于选择查询以产生相同记录的重复结果(Access 2010)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！