Need help correcting record count

[Fivehole91]

i have seen multiple examples of advice on this particular subject, but i dont understand SQL, and therefore don't understand what needs to be done in order to add the "distinct" piece to my query. i only deal with access in its basic form, but here is the SQL version of the query i need help with. if i group by, i get a certain number of records, which is correct, but when i count the loan number field, the result produces additional records. Please help me understand why, if possible, and most importantly, what changes should be made in order to eliminate the erroneous count. thanks in advance!!

SELECT DISTINCT Count(tblSurveys.LoanNum) AS CountOfLoanNum, tblSurveys.AU
FROM tblSurveys LEFT JOIN tblEmployee ON tblSurveys.UnderwriterID = tblEmployee.ID
WHERE (((tblSurveys.MonthReported) Between [Forms]![Data Input]![Start Date1] And ([Forms]![Data Input]![End Date1]+1)))


GROUP BY tblSurveys.AU;

[TheShabz]

If you have an AU of x and LoanNums 1, 1, 1, 3, 5, are you looking for the count to show up as 5 or 3? It looks like from your description that you want it to show up as 3, but I just want to make sure. I dont think using DISTINCT will work in that case. You will need a subquery that will first get you the distinct LoanNums per AU.
First create the following query and call it whatever you want, for the sake of the thread, "qryStep1":

SELECT tblSurveys.AU, tblSurveys.LoanNum
FROM tblSurveys LEFT JOIN tblEmployee ON tblSurveys.UnderwriterID = tblEmployee.ID
WHERE (((tblSurveys.MonthReported) Between [Forms]![Data Input]![Start Date1] And ([Forms]![Data Input]![End Date1]+1)))
GROUP BY
tblSurveys.AU, tblSurveys.LoanNum

Then the following query

SELECT Count(qryStep1.LoanNum) AS CountOfLoanNum, qryStep1.AU
FROM qryStep1
GROUP BY qryStep1.AU

[Fivehole91]

thanks very much for your reply! that did the trick...