tricky SQL problem using VBA Excel to read Access File

[nrgguy]

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.

 

[Guest]

Without the data it is somewhat difficult to see what is going on; try your
SQL after

1. replacing INNER JOIN by a comma
2. removing the ON clause and including its condition in the WHERE clause
instead.

 

[nrgguy]

Thanks for the advice, but I already tried that. I managed to resolve
this on my own by adding another layer of nesting. To restate the
problem, there are items, orders and, customers. I am selecting
orders that contain a certain item# in a certain date range (the inner
most select) and then trying to count (among these orders) the number
of unique orders by customer that have a different item#. My query
could successsfully sum the the quantity and cost of this different
item# for each customer but couldn't give me a unique count of orders
that included the item -- instead the count counted the number of items
meeting the criteria, not unique orders.

I solved it by changing the above query to sum up the quantity, cost
etc. by order (not by customer) and then nest all of it within another
select statement that summed those order subtotals by customer, which
could then properly count the number of orders. Whew!

 

[nrgguy]

Thanks for the advice, but I already tried that. I managed to resolve
this on my own by adding another layer of nesting. To restate the
problem, there are items, orders and, customers. I am selecting
orders that contain a certain item# in a certain date range (the inner
most select) and then trying to count (among these orders) the number
of unique orders by customer that have a different item#. My query
could successsfully sum the the quantity and cost of this different
item# for each customer but couldn't give me a unique count of orders
that included the item -- instead the count counted the number of items
meeting the criteria, not unique orders.

I solved it by changing the above query to sum up the quantity, cost
etc. by order (not by customer) and then nest all of it within another
select statement that summed those order subtotals by customer, which
could then properly count the number of orders. Whew!