How do I sum a count, access db, sql query
Posted on 2008-06-21
I use the query below to tell me how many unique custName entries there are for each location.
SELECT Location, Team, sum(EffectiveRevenueReal), COUNT(custName) AS DistinctNo From vwResults6 WHERE ressource=0 group by Location, Team ORDER BY COUNT(custName) DESC
I now need to sum a grand total of all of the unique custName counts. I have tried
SELECT SUM(c) FROM (SELECT Location, ressource, Count(custName) AS c FROM vwResults6 WHERE ressource=0 GROUP BY Location, ressource) As b
However, when I try to print b, I get the error:
Item cannot be found in the collection corresponding to the requested name or ordinal.