duplicate query: expression not part of statistical function

[easyit]

Hi,


I have a query the results in a calculated column with percentages. If there are duplicates in this column I need to identify them. So I tried a duplicate query which results in the mentioned error. If I use the duplicate criterie query it runs fine. I don't know what is the problem. The sql:

SELECT [TW2013_2B-b_1].[100%], [TW2013_2B-b_1].[fk_praktijkinstelling], [TW2013_2B-b_1].[Naam]
FROM [TW2013_2B-b_1]
WHERE ((([TW2013_2B-b_1].[100%]) In (SELECT Tmp.[100%] FROM [TW2013_2B-b_1] AS Tmp GROUP BY Tmp.[100%] HAVING (((Count(*))>1)))))
ORDER BY [TW2013_2B-b_1].[100%];

the underlying query:

SELECT TW2013_bestaande_pi_behoefte.fk_praktijkinstelling, TW2013_bestaande_pi_behoefte.Naam, TW2013_bestaande_pi_behoefte.[%] AS behoefte, TW2013_bestaande_pi_resultaat.[%] AS historisch, TW2013_bestaande_pi_behoefte.[sector opl], IIf([historisch]<[behoefte],[historisch],[behoefte]) AS laagste, [TW2013_2B-b_1_totalen_percentages].totaal, ([laagste])/[totaal] AS [100%]
FROM [TW2013_2B-b_1_totalen_percentages] INNER JOIN (TW2013_bestaande_pi_resultaat INNER JOIN TW2013_bestaande_pi_behoefte ON (TW2013_bestaande_pi_behoefte.[sector opl] = TW2013_bestaande_pi_resultaat.[Sector-OPl]) AND (TW2013_bestaande_pi_resultaat.Naam = TW2013_bestaande_pi_behoefte.Naam)) ON [TW2013_2B-b_1_totalen_percentages].[sector opl] = TW2013_bestaande_pi_resultaat.[Sector-OPl];

EasyIT

"Do you think that’s air you're breathing?

 

[easyit]

well, sort of solved it myself;

in the query before the underlying query there was a group by that should have been a where. It eliminated 5 unwanted results AND solved the problem.

Happy to move forward - puzzled about the why....



EasyIT

"Do you think that’s air you're breathing?