向导生成的查找重复项的查询中的Access 2007错误

Question

我正在尝试使用查询向导生成一个查询，以查找重复的记录。生成的查询在表中运行良好，但当我再次应用向导在查询中运行查找重复项时，我一直收到以下错误：“您尝试执行不包含聚合函数的指定expression...as部分的查询”。下面是向导生成的SQL：

SELECT [four letter c-codes].[4_letter_code], [four letter c-codes].[bird_data_ID], [four letter c-codes].[Metal_ID]
FROM [four letter c-codes]
WHERE ((([four letter c-codes].[4_letter_code]) In (SELECT [4_letter_code] FROM [four letter c-codes] As Tmp GROUP BY [4_letter_code] HAVING Count(*)>1 )))
ORDER BY [four letter c-codes].[4_letter_code];

我尝试单独执行In子句，它成功了，输出了字段4_letter_code的重复值。用由此产生的常量替换In子句也提供了所需的输出。如果能帮你解决这个问题.

Answer 1

我刚去掉了多余的括号，试试这个.

SELECT  [four letter c-codes].[4_letter_code] ,
        [four letter c-codes].[bird_data_ID] ,
        [four letter c-codes].[Metal_ID]
FROM    [four letter c-codes]
WHERE   [four letter c-codes].[4_letter_code] IN (
        SELECT  [4_letter_code]
        FROM    [four letter c-codes]
        GROUP BY [4_letter_code]
        HAVING  COUNT(*) > 1 )
ORDER BY [four letter c-codes].[4_letter_code];

或者，你可以试试INNER JOIN.

SELECT  [four letter c-codes].[4_letter_code] ,
        [four letter c-codes].[bird_data_ID] ,
        [four letter c-codes].[Metal_ID]
FROM    [four letter c-codes]
        INNER JOIN ( SELECT [4_letter_code]
                     FROM   [four letter c-codes]
                     GROUP BY [4_letter_code]
                     HAVING COUNT(*) > 1
                   ) b 
             ON [four letter c-codes].[4_letter_code] = b.[4_letter_code]
ORDER BY [four letter c-codes].[4_letter_code];