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":
Code:
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
Code:
SELECT Count(qryStep1.LoanNum) AS CountOfLoanNum, qryStep1.AU
FROM qryStep1
GROUP BY qryStep1.AU