Solved

How do I sum a count, access db, sql query

Posted on 2008-06-21
2
704 Views
Last Modified: 2008-06-22
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.
0
Comment
Question by:hologosesh
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 21838372
the "as b" names the subquery, and not the sum(c) expression...
SELECT SUM(c) as b FROM (SELECT Location, ressource, Count(custName) AS c FROM vwResults6 WHERE ressource=0 GROUP BY Location, ressource) As subquery

Open in new window

0
 

Author Comment

by:hologosesh
ID: 21841707
Great, thanks.  Works perfectly.  
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question