Select query count function help

[Brian Colfax]

Hello,

I have a query that selects unique acct #'s based on criteria in two tables
(loan type, loan status, date, etc). The status code in the original table
is run through a module that groups similar codes together. What I want to do
is return the number of unique acct #'s and the total $$ for each group of
status codes.

The problem is, when I run the query with Group By in the Totals row for the
Acct # field I get a certain number of records (several thousand), which I
assume is the correct number; but when I change "Group By" to "Count", the
count it returns is always higher than the original.

Any idea what I'm doing wrong? Here is the SQL.

SELECT DISTINCT [Groupfile(tst)].SSN, constat([STAT]) AS [Group Status],
Sum([Loanfile(tst)].cpb) AS SumOfcpb, FYYear([App Date]) AS [App FY]
FROM [Loanfile(tst)] INNER JOIN [Groupfile(tst)] ON ([Loanfile(tst)].grp_id
= [Groupfile(tst)].GRP_ID) AND ([Loanfile(tst)].SSN = [Groupfile(tst)].SSN)
WHERE ((([Groupfile(tst)].PmtCount)>-999) AND
(([Groupfile(tst)].LN_TYPE)="cons") AND (([Groupfile(tst)].STAT)<"P96") AND
(([Loanfile(tst)].[App Date])>#6/30/2005#))
GROUP BY [Groupfile(tst)].SSN, constat([STAT]), FYYear([App Date])
HAVING (((constat([STAT]))="other"))
ORDER BY Sum([Loanfile(tst)].cpb) DESC;

Thanks in advance.

 

[Brian Colfax]

I think I figured it out the problem, but I still don't know how to fix it.
My two tables (Groupfile and Loanfile) have a different number of records
for each acct #, because there can be more than one loan in a group. The
Count function is returning a loan count instead of an acct # count.