Distinct in Access ?? Is not working

[hello101]

Hi everyone,

I'm trying to run this query and it runs fun but returning the wrong results. I need to use Distinct on count in access and i don't know the syntax. if i add DISTINCT before count it does not work! Do i need to use a subquery?
Any ideas..

Tks in advance


SELECT D.DayOfYear, Count(S.M_ID) AS CountOfActive INTO [Return#ofMperdate]
FROM Mas AS D, Loan AS S
WHERE (((D.DayOfYear) Between #1/1/2006# And Date()) AND ((S.HIRE_DATE)<=[D].[DayOfYear]) AND ((S.TERM_DATE)>=[D].[DayOfYear] Or (S.TERM_DATE) Is Null))
GROUP BY D.DayOfYear;

 

[kjv1611]

Using GROUP BY, as you have here, should be the same as using DISTINCT - you don't need the DISTINCT command in this instance.