I'm having trouble with a somewhat complex SQL query I'm running from
Excel VBA to query an Access database. I'm trying to get as part of
the results the number of unique AccountNos served by each AuditorID
that meet all of the criteria, but instead the count is coming up as
the number of transactions (more than 1 per Account can be common).

The sql command is (broken up for readability here):

SELECT [Meas].[AuditorID], Count([HUacct].[AccountNo]) AS [Nunitlight],
Sum([Meas].[Qty]) AS [Nlight] FROM tblMeasureInstall Meas

INNER JOIN

(SELECT DISTINCTROW [TMI].[AccountNo]
FROM tblMeasureInstall TMI
WHERE ( [TMI].[InstallDate]<NULL AND ([TMI].[InstallDate] BETWEEN
#07/01/2005# AND #12/31/2005#) And ([TMI].[Canceled]<True) AND
([TMI].[MeasureID] in (62,63,77)) And ([TMI].[MeasureGroupID]=6) )
GROUP BY [TMI].[AccountNo] ) HUacct

ON [Meas].[AccountNo] = [HUacct].[AccountNo]

WHERE ( [Meas].[Canceled]<True AND [Meas].[MeasureGroupID]=3 ) GROUP
BY [AuditorID];

What I'm trying to get for the second column using
Count([HUacct].[AccountNo])
is a count of the number of unique AccountNos found in the table HUacct
(created by the nested second SELECT command) that are matched into
tblMeasureInstall. The HUacct table is grouped by AccountNo and has
DISTINCTROW, so it should be one record per AccountNo (which a separate
query confirms). But somehow after the join, my COUNT() comes up with
the number of qualifying transactions for all accounts of a given
AuditorID. The other results look OK. I've tried changing the JOIN
type from INNER to OUTER and RIGHT and LEFT and haven't gotten
anywhere. I'd appreciate any SQL-guru who could help.