Group by with one field Unique Count

[Jody]

I am running a group by query that most of the fields are summed, but the
last field I want it to count but not every record I only want it to count
the unique entries, basicly here is the total production and how many
dealer's the production came from.

SELECT [Credit Guard Report].Region, Sum([Credit Guard Report].Unemployment)
AS SumOfUnemployment, Sum([Credit Guard Report].Life) AS SumOfLife,
Sum([Credit Guard Report].[Unemp-Death]) AS [SumOfUnemp-Death], Sum([Credit
Guard Report].[# Covered]) AS [SumOf# Covered], Count([Credit Guard
Report].Dealer) AS CountOfDealer

FROM [Credit Guard Report]
GROUP BY [Credit Guard Report].Region;

Any suggestions would greatly help.

 

[John Spencer]

Access doesn't support Count unique.

You have to return a unique set and then get the count.

I'm not sure this will work, but you can try putting this in your select clause
SELECT Region, Sum(Unemployment) as SumUnemployment,
(SELECT Count(*) FROM
(SELECT DISTINCT Dealer
FROM [Credit Guard Report]) as X) As DealerCount
, ...
FROM [Credit Guard Report]
GROUP BY ...


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County