troubleshooting Question

SUM of COUNTS

Avatar of JLohman
JLohman asked on
Microsoft AccessASPSQL
39 Comments3 Solutions852 ViewsLast Modified:
I am trying to get the SUM of COUNTS.

I have the following code which is counting correctly:
'Count the number of POSITION #1 VACANCIES
      sqlCountVacancies1 = "SELECT Count(tblStaff.PositionStatus1) AS CountVacancies1 " & _
      "FROM tblStaff " & _
      "WHERE tblStaff.PositionStatus1 = 'Vacant' "

      Set objCountVacancies1 = Server.CreateObject("ADODB.Recordset")
      objCountVacancies1.Open sqlCountVacancies1, objConn

I have 10 fields that I am counting: PositionStatus(1-10).
I now want to get a count of the total:
   CountVacancies1 + CountVacancies2 + CountVacancies3 + ..... CountVacancies10

I tried the following code:
'Count the TOTAL NUMBER OF VACANCIES
      sqlSumVacancies = "SELECT Sum(Count(CountVacancies1) + Count(CountVacancies2) + Count(CountVacancies3) + Count(CountVacancies4) + Count(CountVacancies5) + Count(CountVacancies6) + Count(CountVacancies7) + Count(CountVacancies8) + Count(CountVacancies9) + Count(CountVacancies10)) AS SumVacancies " & _

      Set objSumVacancies = Server.CreateObject("ADODB.Recordset")
      objSumVacancies.Open sqlSumVacancies, objConn

and get a Syntax error:

Set objSumVacancies = Server.CreateObject("ADODB.Recordset")
^

ASKER CERTIFIED SOLUTION
JLohman

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 39 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 39 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros