时间戳

首页 > 解决方案 > 如何修复访问查询中的重复项

sql - 如何修复访问查询中的重复项

问题描述

我有一个查询应该返回每个人的计数和平均值，但是我得到了人们的重复值。我检查了名称的格式，但不知道为什么我仍然得到重复。

 SELECT 
      AssetScores.Department, AssetScores.Evaluator, (Avg(AssetScores.[Final 
      Score]/100)) AS [Average Score], Count(AssetScores.[Final Score]) AS 
      Completed
 FROM 
      AssetScores
 WHERE 
     (((Month([Date of Review]))=Month(Now())) AND ((Year([Date of 
        Review]))=Year(Now())))
 GROUP BY 
     AssetScores.Department, AssetScores.Evaluator, AssetScores.[date of 
     review];

预期成绩：

Department    Evaluator   Average Score     Completed
Collections   Melissa Martinez  1           1
Collections   Qiana Bridges         1           3
Collections   Robert Sutowski   1           1
Profit & Loss     Wanda Tavitas         1           2
Recovery      Pete Rodriguez    1           2
Servicing     Crystal Zamarripa     1          11
Servicing     Richard Paez          1          11
Speciality        Patrick Ryan          0.95            8
Vendor Relations  Monica Garza          0.92           10

当前结果

Department    Evaluator   Average Score     Completed
Collections   Melissa Martinez  1           1
Collections   Qiana Bridges         1           3
Collections   Robert Sutowski   1           1
Profit & Loss     Wanda Tavitas         1           1
Profit & Loss     Wanda Tavitas         1           1
Recovery      Pete Rodriguez    1           2
Servicing     Crystal Zamarripa     1          11
Servicing     Richard Paez          1          11
Speciality        Patrick Ryan          0.95            8
Vendor Relations  Monica Garza          0.95           10
Vendor Relations  Monica Garza          0.90            9

标签： sqlms-access

解决方案

GROUP BY您的查询当前在子句中有 3 个非聚合字段：

 GROUP BY 
    AssetScores.Department, 
    AssetScores.Evaluator, 
    AssetScores.[date of review]

但是，从您的预期输出来看，您似乎不需要审查日期。我建议将GROUP BY条款更改为：

 GROUP BY 
    AssetScores.Department, 
    AssetScores.Evaluator

推荐阅读