Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SUM of COUNTS

Posted on 2011-03-09
39
3 solutions
Medium Priority
?
826 Views
Last Modified: 2012-05-11
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")
^

0
Comment
Question by:JLohman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Join The Community
  • 20
  • 9
  • 4
  • +2
39 Comments
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35086728
I think an easier way to do this would be to use a group by statement:

Select Sum(count(tblStaff.PositionStatus1))
From tblStaff
Group by tblStaff.PositionStatus1

Open in new window


This will return the sum of the count of all the different possible values of tblStaff.PositionStatus1.

You could add a where clause(in between the From and Group by clause for all specific tblStaff.PositionStatus1's you'd like such as
WHERE tblStaff.PositionStatus1='this' OR tblStaff.PositionStatus1='that' OR...

Open in new window


and so on
0
 

Author Comment

by:JLohman
ID: 35086895
Sorry, I don't think I explained correctly.

I have 10 fields:
PositionStatus1
PositionStatus2
PositionStatus3
PositionStatus4
PositionStatus5
PositionStatus6
PositionStatus7
PositionStatus8
PositionStatus9
PositionStatus10

The first calculation is to count WHERE the Position status = Vacant (this is working)
The 2nd calculation that I am trying to do is to count the TOTAL of all the above.
0
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35086935
Oh, i see, well in that case your select statement doesn't need the SUM function, you can just add up all the counts, so it should be...
SELECT (Count(CountVacancies1) + Count(CountVacancies2) + Count(CountVacancies3) + Count(CountVacancies4) + Count(CountVacancies5) + Count(CountVacancies6) + Count(CountVacancies7) + Count(CountVacancies8) + Count(CountVacancies9) + Count(CountVacancies10)) AS SumVacancies

Open in new window


You should be able to add up the individual counts.  The SUM function is more used to SUM up a specific column of data.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Take Survey Now!
 

Author Comment

by:JLohman
ID: 35087018
I am getting a 'Expected Case' error.

'Count the TOTAL NUMBER OF VACANCIES
SELECT (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

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35087040
>> The first calculation is to count WHERE the Position status = Vacant (this is working)
Do you have another column as Position Status (or) Are you referring PositionStatus1, PositionStatus2 etc in the WHERE clause?
0
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35087144
If you are doing each count(PositionStatus[1-10]) seperately already, is it possible for you to store those values into an array, or into seperate variables, and just add them up normally.  Or do you have to do this using SQL?
0
 

Author Comment

by:JLohman
ID: 35087179
To calculate the totals WHERE the 10 fields (PositionStatus(1-10) = VACANT I am using the following code for each field (changing the number from 1 to 10)

'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 then want a sum of all the CountVacancies. I don't think i need a WHERE clause in this statement.
I want a TOTAL of

CountVacancies1 + CountVacancies2 + CountVacancies3 .....
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35087355
I think you are looking for something like this.
SELECT CountVacancies1 + CountVacancies2 + CountVacancies3 + CountVacancies4 + CountVacancies5 + CountVacancies6 + CountVacancies7 + CountVacancies8 + CountVacancies9 + CountVacancies10
  FROM (SELECT SUM(CASE 
                     WHEN PositionStatus1 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies1, 
               SUM(CASE 
                     WHEN PositionStatus2 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies2, 
               SUM(CASE 
                     WHEN PositionStatus3 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies3, 
               SUM(CASE 
                     WHEN PositionStatus4 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies4, 
               SUM(CASE 
                     WHEN PositionStatus5 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies5, 
               SUM(CASE 
                     WHEN PositionStatus6 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies6, 
               SUM(CASE 
                     WHEN PositionStatus7 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies7, 
               SUM(CASE 
                     WHEN PositionStatus8 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies8, 
               SUM(CASE 
                     WHEN PositionStatus9 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies9, 
               SUM(CASE 
                     WHEN PositionStatus10 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies10 
          FROM tblStaff) t1

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35088222
i am not clear which scenario you are wanting...

does the sum of the individual column counts have meaning  query 1

or do you really want query 2 which counts the occurences of any row with a 'Vacant' status?


sqlCountVacancies = "SELECT Count(case PositionStatus1 when 'Vacant' then 1 else 0 end) " & _
"+ Count(case PositionStatus2 when 'Vacant' then 1 else 0 end)  " & _
"+ Count(case PositionStatus3 when 'Vacant' then 1 else 0 end)  " & _  
"+ Count(case PositionStatus4 when 'Vacant' then 1 else 0 end)  " & _
"+ Count(case PositionStatus5 when 'Vacant' then 1 else 0 end)  " & _  
"+ Count(case PositionStatus6 when 'Vacant' then 1 else 0 end)  " & _
"+ Count(case PositionStatus7 when 'Vacant' then 1 else 0 end)  " & _  
"+ Count(case PositionStatus8 when 'Vacant' then 1 else 0 end)  " & _
"+ Count(case PositionStatus9 when 'Vacant' then 1 else 0 end)  " & _
"+ Count(case PositionStatus10 when 'Vacant' then 1 else 0 end)  " & _  
    "FROM tblStaff " 

or

sqlCountVacancies = "SELECT Count(*) " & _
"FROM tblStaff "  & _
" where 'Vacant' in (positionstatus1,positionstatus2,positionstatus3,positionstatus4" & _
" ,positionstatus5,positionstatus6,positionstatus7,positionstatus8,positionstatus9,positionstatus10)"

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35088278
also why are you submitting 10 queries to the database rather than 1 query and looping through the result set for the
10 /11 counts?
0
 

Author Comment

by:JLohman
ID: 35089475
I want the display for each field plus a total:

Number of Position 1 vacancies: 16
Number of Position 2 vacancies: 14
Number of Position 3 vacancies: 13
Number of Position 4 vacancies: 5
Number of Position 5 vacancies: 2
Number of Position 6 vacancies: 4
Number of Position 7 vacancies: 2
Number of Position 8 vacancies: 0
Number of Position 9 vacancies: 0
Number of Position 10 vacancies: 0
Total vacancies: 43

The attached code for the TOTAL is close but it is ONLY counting the first 3 fields.
'Count the TOTAL NUMBER OF VACANCIES
  sqlCountVacancies = "SELECT Count(*) AS CountVacancies " & _
  "FROM tblStaff "  & _
  "WHERE 'Vacant' in (positionstatus1,positionstatus2,positionstatus3,positionstatus4,positionstatus5,positionstatus6,positionstatus7,positionstatus8,positionstatus9,positionstatus10) " 

  Set objCountVacancies = Server.CreateObject("ADODB.Recordset")
  objCountVacancies.Open sqlCountVacancies, objConn

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35089508
Is you Total count correct? Is it not the sum of all vacancies (position 1,2,3 etc)?
0
 

Author Comment

by:JLohman
ID: 35089529
No the total count is not correct, the display above is the real data. The total does not add up.
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 400 total points
ID: 35089550
Did you try my query? What is your comment on that?
SELECT CountVacancies1, 
       CountVacancies2, 
       CountVacancies3, 
       CountVacancies4, 
       CountVacancies5, 
       CountVacancies6, 
       CountVacancies7, 
       CountVacancies8, 
       CountVacancies9, 
       CountVacancies10, 
       CountVacancies1 + CountVacancies2 + CountVacancies3 + CountVacancies4 + CountVacancies5 + CountVacancies6 + CountVacancies7 + CountVacancies8 + CountVacancies9 + CountVacancies10 TotalCount
  FROM (SELECT SUM(CASE 
                     WHEN PositionStatus1 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies1, 
               SUM(CASE 
                     WHEN PositionStatus2 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies2, 
               SUM(CASE 
                     WHEN PositionStatus3 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies3, 
               SUM(CASE 
                     WHEN PositionStatus4 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies4, 
               SUM(CASE 
                     WHEN PositionStatus5 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies5, 
               SUM(CASE 
                     WHEN PositionStatus6 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies6, 
               SUM(CASE 
                     WHEN PositionStatus7 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies7, 
               SUM(CASE 
                     WHEN PositionStatus8 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies8, 
               SUM(CASE 
                     WHEN PositionStatus9 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies9, 
               SUM(CASE 
                     WHEN PositionStatus10 = 'Vacant' THEN 1 
                     ELSE 0 
                   END) CountVacancies10 
          FROM tblStaff) t1

Open in new window

0
 

Author Comment

by:JLohman
ID: 35089950
Sharath123: I am getting an Invalid Character with the code at the 1st FROM line. I attaching the code I am using. I have never used SUM(CASE. I don't see what variable you are using for the TOTAL.
'Count the TOTAL NUMBER OF VACANCIES
sql CountVacanciesTotal = "SELECT CountVacancies1, " & _
      "CountVacancies2, " & _ 
      "CountVacancies3, " & _ 
      "CountVacancies4, " & _ 
      "CountVacancies5, " & _ 
      "CountVacancies6, " & _ 
      "CountVacancies7, " & _ 
      "CountVacancies8, " & _ 
      "CountVacancies9,  " & _
      "CountVacancies10, " & _ 
      "CountVacancies1 + CountVacancies2 + CountVacancies3 + CountVacancies4 + CountVacancies5 + CountVacancies6 + CountVacancies7 + CountVacancies8 + CountVacancies9 + CountVacancies10 AS TotalCount " & _
  	"FROM (SELECT SUM(CASE WHEN PositionStatus1 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies1, " _ &
               "SUM(CASE WHEN PositionStatus2 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies2, " _ & 
               "SUM(CASE WHEN PositionStatus3 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies3, " _ & 
               "SUM(CASE WHEN PositionStatus4 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies4, " _ & 
               "SUM(CASE WHEN PositionStatus5 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies5, " _ & 
               "SUM(CASE WHEN PositionStatus6 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies6, " _ & 
               "SUM(CASE WHEN PositionStatus7 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies7, " _ & 
               "SUM(CASE WHEN PositionStatus8 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies8, " _ & 
               "SUM(CASE WHEN PositionStatus9 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies9, " _ & 
               "SUM(CASE WHEN PositionStatus10 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies10) " _ & 
          "FROM tblStaff) "

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35089956
The attached code for the TOTAL is close but it is ONLY counting the first 3 fields.

NO

it is not just counting the first 3 fields.... WE ARE COUNTING ROWS

so the first query should have given the correct/desired result....
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35089964
on sharath#s example line 23 should read

 "FROM tblStaff) as x"


0
 

Author Comment

by:JLohman
ID: 35090615
I am apparently not explaining correctly.

I have a table in MSAccess:  tblStaff
I have 10 fields:
- PositionStatus1
- PositionStatus2
- PositionStatus3
- PositionStatus4
- PositionStatus5
- PositionStatus6
- PositionStatus7
- PositionStatus8
- PositionStatus9
- PositionStatus10

Code is attached.

My display is:  

Number of Position 1 vacancies: 16
Number of Position 2 vacancies: 14
Number of Position 3 vacancies: 13
Number of Position 4 vacancies: 5
Number of Position 5 vacancies: 2
Number of Position 6 vacancies: 4
Number of Position 7 vacancies: 2
Number of Position 8 vacancies: 0
Number of Position 9 vacancies: 0
Number of Position 10 vacancies: 0
Total vacancies: 43

The total vacancies should equal 56.
<%
'Create recordset
 Dim sql, objRS

sql = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, " & _
 		"tblGeneral.Notation, tblGeneral.ConsolidationNote, tblGeneral.AllHCF, " & _
 		"tblStaff.PositionStatus1, tblStaff.PositionStatus2, tblStaff.PositionStatus3, " & _
 		"tblStaff.PositionStatus4, tblStaff.PositionStatus5, tblStaff.PositionStatus6, " & _
 		"tblStaff.PositionStatus7, tblStaff.PositionStatus8, tblStaff.PositionStatus9, " & _
 		"tblStaff.PositionStatus10, tblStaff.LibFTE, " & _
 		"tblStaff.LibManTitle, tblStaff.LibManName, tblStaff.Title1, tblStaff.StaffGenNotes " & _
		"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID "
	
	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open sql, objConn


'------------------------------------------
'COUNTS
'------------------------------------------
'Count the number of all HCF facilities
	sqlCountAllHCF = "SELECT COUNT(tblGeneral.AllHCF) AS CountAllHCF " & _
	"FROM tblGeneral " & _
	"WHERE tblGeneral.AllHCF = 'Yes' " 

	Set objCountAllHCF = Server.CreateObject("ADODB.Recordset")
	objCountAllHCF.Open sqlCountAllHCF, objConn


'Count the FTE Total
	sqlCountLibFTE = "SELECT Count(tblStaff.LibFTE) AS CountLibFTE " & _
	"FROM tblStaff "

	Set objCountLibFTE = Server.CreateObject("ADODB.Recordset")
	objCountLibFTE.Open sqlCountLibFTE, objConn

'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

'Count the number of POSITION #2 VACANCIES
	sqlCountVacancies2 = "SELECT Count(tblStaff.PositionStatus2) AS CountVacancies2 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus2 = 'Vacant' "

	Set objCountVacancies2 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies2.Open sqlCountVacancies2, objConn

'Count the number of POSITION #3 VACANCIES
	sqlCountVacancies3 = "SELECT Count(tblStaff.PositionStatus3) AS CountVacancies3 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus3 = 'Vacant' "

	Set objCountVacancies3 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies3.Open sqlCountVacancies3, objConn

'Count the number of POSITION #4 VACANCIES
	sqlCountVacancies4 = "SELECT Count(tblStaff.PositionStatus4) AS CountVacancies4 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus4 = 'Vacant' "

	Set objCountVacancies4 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies4.Open sqlCountVacancies4, objConn

'Count the number of POSITION #5 VACANCIES
	sqlCountVacancies5 = "SELECT Count(tblStaff.PositionStatus5) AS CountVacancies5 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus5 = 'Vacant' "

	Set objCountVacancies5 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies5.Open sqlCountVacancies5, objConn

'Count the number of POSITION #6 VACANCIES
	sqlCountVacancies6 = "SELECT Count(tblStaff.PositionStatus6) AS CountVacancies6 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus6 = 'Vacant' "

	Set objCountVacancies6 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies6.Open sqlCountVacancies6, objConn

'Count the number of POSITION #7 VACANCIES
	sqlCountVacancies7 = "SELECT Count(tblStaff.PositionStatus7) AS CountVacancies7 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus7 = 'Vacant' "

	Set objCountVacancies7 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies7.Open sqlCountVacancies7, objConn

'Count the number of POSITION #8 VACANCIES
	sqlCountVacancies8 = "SELECT Count(tblStaff.PositionStatus8) AS CountVacancies8 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus8 = 'Vacant' "

	Set objCountVacancies8 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies8.Open sqlCountVacancies8, objConn

'Count the number of POSITION #9 VACANCIES
	sqlCountVacancies9 = "SELECT Count(tblStaff.PositionStatus9) AS CountVacancies9 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus9 = 'Vacant' "

	Set objCountVacancies9 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies9.Open sqlCountVacancies9, objConn

'Count the number of POSITION #10 VACANCIES
	sqlCountVacancies10 = "SELECT Count(tblStaff.PositionStatus10) AS CountVacancies10 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus10 = 'Vacant' "

	Set objCountVacancies10 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies10.Open sqlCountVacancies10, objConn

'Count the TOTAL NUMBER OF VACANCIES
	sqlCountVacancies = "SELECT Count(*) AS CountVacancies " & _
	"FROM tblStaff "  & _
	"WHERE 'Vacant' IN (PositionStatus1,PositionStatus2,PositionStatus3,PositionStatus4,PositionStatus5,PositionStatus6,PositionStatus7,PositionStatus8,PositionStatus9,PositionStatus10) " 

	Set objCountVacancies = Server.CreateObject("ADODB.Recordset")
	objCountVacancies.Open sqlCountVacancies, objConn

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35096017
<Just a quick comment>

FWIW, (If i am understanding the basic issue here)

Perhaps your data should be "Normalized" into one field.
So instead of doing things like this:
=NZ(Fld1)+NZ(Fld2)+NZ(Fld3)...

You can simply do this:
=Sum(SomeField)

Again, just a comment, I'll let you continue with previous experts...

JeffCoachman
0
 

Author Comment

by:JLohman
ID: 35096931
It isn't a sum of just one field, I am trying to get a total of 10 different fields where the data equals the text 'Vacant'.

Right now I am stuck with the following code, it is not correctly adding the results of the 10 separate fields:

'Count the TOTAL NUMBER OF VACANCIES
      sqlCountVacancies = "SELECT Count(*) AS CountVacancies " & _
      "FROM tblStaff "  & _
      "WHERE 'Vacant' IN (PositionStatus1,PositionStatus2,PositionStatus3,PositionStatus4,PositionStatus5,PositionStatus6,PositionStatus7,PositionStatus8,PositionStatus9,PositionStatus10) " 

      Set objCountVacancies = Server.CreateObject("ADODB.Recordset")
      objCountVacancies.Open sqlCountVacancies, objConn

My results(display) looks like this, but the Total Vacancies should be 56!

Number of Position 1 vacancies: 16
Number of Position 2 vacancies: 14
Number of Position 3 vacancies: 13
Number of Position 4 vacancies: 5
Number of Position 5 vacancies: 2
Number of Position 6 vacancies: 4
Number of Position 7 vacancies: 2
Number of Position 8 vacancies: 0
Number of Position 9 vacancies: 0
Number of Position 10 vacancies: 0
Total vacancies: 43

HELP!!!!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35097239
<It isn't a sum of just one field, I am trying to get a total of 10 different fields where the data equals the text 'Vacant'.>
That was my point, your data should be "normalized" into one filed, not multiple fields.
Then this would be easy.
Instead of having this:
Sat, Sun
2,4
3,1
9,7

You would have something like this:
Day, Amt
Sat,2
Sun,4
Sat,3
Sun,1
Sat,9
Sun,7

Again, this is the standard way this is done.

I understand that sometimes changing the Data format is not possible, this is why I stated:  
    <I'll let you continue with previous experts...>

;-)

JeffCoachman
0
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35097389
The reason you are not getting the correct results with your current query is because you most likely have multiple fields set to vacant at a time and your query is counting those instances as 1.  

You need to most likely use Sharath_123's longer query to get the results you want. (with the added fix from Lowfatspread).  Please try this, post the entire query(again) and then post the entire error message.  Thank you.
0
 

Author Comment

by:JLohman
ID: 35098167
I am attaching the code. It functions correctly until I add the sql CountVacanciesTotal code.

I used Sharath's code and modified based on Lowfatspread.

Now I am getting a "Page cannot display"


<%
'Create recordset
 Dim sql, objRS

sql = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, " & _
 		"tblGeneral.Notation, tblGeneral.ConsolidationNote, tblGeneral.AllHCF, " & _
 		"tblStaff.PositionStatus1, tblStaff.PositionStatus2, tblStaff.PositionStatus3, " & _
 		"tblStaff.PositionStatus4, tblStaff.PositionStatus5, tblStaff.PositionStatus6, " & _
 		"tblStaff.PositionStatus7, tblStaff.PositionStatus8, tblStaff.PositionStatus9, " & _
 		"tblStaff.PositionStatus10, tblStaff.LibFTE, " & _
 		"tblStaff.LibManTitle, tblStaff.LibManName, tblStaff.Title1, tblStaff.StaffGenNotes " & _
		"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _

		"WHERE tblStaff.PositionStatus1='Vacant' OR tblStaff.PositionStatus2='Vacant' " & _
		"OR tblStaff.PositionStatus3='Vacant' OR tblStaff.PositionStatus4='Vacant' " & _
		"OR tblStaff.PositionStatus5='Vacant' OR tblStaff.PositionStatus6='Vacant' " & _
		"OR tblStaff.PositionStatus7='Vacant' OR tblStaff.PositionStatus8='Vacant' " & _
		"OR tblStaff.PositionStatus9='Vacant' OR tblStaff.PositionStatus10='Vacant'"

 		If Request.querystring("sort") = "" then
 			sql = sql & "ORDER BY City, State"
 		Else
  			sql = sql & "ORDER BY " & Request.querystring("sort")
  		End If			
	
	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open sql, objConn


'------------------------------------------
'COUNTS
'------------------------------------------
'Count the number of all HCF facilities
	sqlCountAllHCF = "SELECT COUNT(tblGeneral.AllHCF) AS CountAllHCF " & _
	"FROM tblGeneral " & _
	"WHERE tblGeneral.AllHCF = 'Yes' " 

	Set objCountAllHCF = Server.CreateObject("ADODB.Recordset")
	objCountAllHCF.Open sqlCountAllHCF, objConn


'Count the FTE Total
	sqlCountLibFTE = "SELECT Count(tblStaff.LibFTE) AS CountLibFTE " & _
	"FROM tblStaff "

	Set objCountLibFTE = Server.CreateObject("ADODB.Recordset")
	objCountLibFTE.Open sqlCountLibFTE, objConn

'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

'Count the number of POSITION #2 VACANCIES
	sqlCountVacancies2 = "SELECT Count(tblStaff.PositionStatus2) AS CountVacancies2 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus2 = 'Vacant' "

	Set objCountVacancies2 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies2.Open sqlCountVacancies2, objConn

'Count the number of POSITION #3 VACANCIES
	sqlCountVacancies3 = "SELECT Count(tblStaff.PositionStatus3) AS CountVacancies3 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus3 = 'Vacant' "

	Set objCountVacancies3 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies3.Open sqlCountVacancies3, objConn

'Count the number of POSITION #4 VACANCIES
	sqlCountVacancies4 = "SELECT Count(tblStaff.PositionStatus4) AS CountVacancies4 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus4 = 'Vacant' "

	Set objCountVacancies4 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies4.Open sqlCountVacancies4, objConn

'Count the number of POSITION #5 VACANCIES
	sqlCountVacancies5 = "SELECT Count(tblStaff.PositionStatus5) AS CountVacancies5 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus5 = 'Vacant' "

	Set objCountVacancies5 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies5.Open sqlCountVacancies5, objConn

'Count the number of POSITION #6 VACANCIES
	sqlCountVacancies6 = "SELECT Count(tblStaff.PositionStatus6) AS CountVacancies6 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus6 = 'Vacant' "

	Set objCountVacancies6 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies6.Open sqlCountVacancies6, objConn

'Count the number of POSITION #7 VACANCIES
	sqlCountVacancies7 = "SELECT Count(tblStaff.PositionStatus7) AS CountVacancies7 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus7 = 'Vacant' "

	Set objCountVacancies7 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies7.Open sqlCountVacancies7, objConn

'Count the number of POSITION #8 VACANCIES
	sqlCountVacancies8 = "SELECT Count(tblStaff.PositionStatus8) AS CountVacancies8 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus8 = 'Vacant' "

	Set objCountVacancies8 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies8.Open sqlCountVacancies8, objConn

'Count the number of POSITION #9 VACANCIES
	sqlCountVacancies9 = "SELECT Count(tblStaff.PositionStatus9) AS CountVacancies9 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus9 = 'Vacant' "

	Set objCountVacancies9 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies9.Open sqlCountVacancies9, objConn

'Count the number of POSITION #10 VACANCIES
	sqlCountVacancies10 = "SELECT Count(tblStaff.PositionStatus10) AS CountVacancies10 " & _
	"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.PositionStatus10 = 'Vacant' "

	Set objCountVacancies10 = Server.CreateObject("ADODB.Recordset")
	objCountVacancies10.Open sqlCountVacancies10, objConn

'Count the TOTAL NUMBER OF VACANCIES
sql CountVacanciesTotal = "SELECT CountVacancies1, " & _
      "CountVacancies2, " & _ 
      "CountVacancies3, " & _ 
      "CountVacancies4, " & _ 
      "CountVacancies5, " & _ 
      "CountVacancies6, " & _ 
      "CountVacancies7, " & _ 
      "CountVacancies8, " & _ 
      "CountVacancies9,  " & _
      "CountVacancies10, " & _ 
      "CountVacancies1 + CountVacancies2 + CountVacancies3 + CountVacancies4 + CountVacancies5 + CountVacancies6 + CountVacancies7 + CountVacancies8 + CountVacancies9 + CountVacancies10 AS TotalCount " & _
  	"FROM (SELECT SUM(CASE WHEN PositionStatus1 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies1, " 
               "SUM(CASE WHEN PositionStatus2 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies2, " _ & 
               "SUM(CASE WHEN PositionStatus3 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies3, " _ & 
               "SUM(CASE WHEN PositionStatus4 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies4, " _ & 
               "SUM(CASE WHEN PositionStatus5 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies5, " _ & 
               "SUM(CASE WHEN PositionStatus6 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies6, " _ & 
               "SUM(CASE WHEN PositionStatus7 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies7, " _ & 
               "SUM(CASE WHEN PositionStatus8 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies8, " _ & 
               "SUM(CASE WHEN PositionStatus9 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies9, " _ & 
               "SUM(CASE WHEN PositionStatus10 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies10) " _ & 
          "FROM tblStaff) as CountVacanciesTotal "

	Set objCountVacanciesTotal = Server.CreateObject("ADODB.Recordset")
	objCountVacanciesTotal.Open sqlCountVacanciesTotal, objConn






Response.Write "<ul>"
Response.Write "<li><font face='Arial' font size=2'>Total number of facilities with Libraries (ALL HCF facilities):  "
  	Response.write (objCountAllHCF("CountAllHCF")) 
Response.Write "</li>"

Response.Write "<li><font face='Arial' font size='2'>Total FTE:  " 
	Response.write (objCountLibFTE("CountLibFTE"))
Response.Write "</li>"

Response.Write "<li><font face='Arial' font size='2'>Number of Position 1 vacancies:  " 
	Response.write (objCountVacancies1("CountVacancies1"))
Response.Write "</li>"

Response.Write "<li><font face='Arial' font size='2'>Number of Position 2 vacancies:  " 
	Response.write (objCountVacancies2("CountVacancies2"))
Response.Write "</li>"
	
Response.Write "<li><font face='Arial' font size='2'>Number of Position 3 vacancies:  " 
	Response.write (objCountVacancies3("CountVacancies3"))
Response.Write "</li>"

Response.Write "<li><font face='Arial' font size='2'>Number of Position 4 vacancies:  " 
	Response.write (objCountVacancies4("CountVacancies4"))
Response.Write "</li>"
	
Response.Write "<li><font face='Arial' font size='2'>Number of Position 5 vacancies:  " 
	Response.write (objCountVacancies5("CountVacancies5"))
Response.Write "</li>"
	
Response.Write "<li><font face='Arial' font size='2'>Number of Position 6 vacancies:  " 
	Response.write (objCountVacancies6("CountVacancies6"))
Response.Write "</li>"
	
Response.Write "<li><font face='Arial' font size='2'>Number of Position 7 vacancies:  " 
	Response.write (objCountVacancies7("CountVacancies7"))
Response.Write "</li>"
	
Response.Write "<li><font face='Arial' font size='2'>Number of Position 8 vacancies:  " 
	Response.write (objCountVacancies8("CountVacancies8"))
Response.Write "</li>"
	
Response.Write "<li><font face='Arial' font size='2'>Number of Position 9 vacancies:  " 
	Response.write (objCountVacancies9("CountVacancies9"))
Response.Write "</li>"
	
Response.Write "<li><font face='Arial' font size='2'>Number of Position 10 vacancies:  " 
	Response.write (objCountVacancies10("CountVacancies10"))
Response.Write "</li>"
	
Response.Write "<li><font face='Arial' font size='2'>Total vacancies:  " 
	Response.write (objCountVacanciesTotal("CountVacanciesTotal"))
Response.Write "</li>"
	



Response.Write "</ul>"

Open in new window

0
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35098254
Because you are doing each vacancy separately, change the total code to look like this:
sql CountVacanciesTotal = "SELECT  " & _ 
      "CountVacancies1 + CountVacancies2 + CountVacancies3 + CountVacancies4 + CountVacancies5 + CountVacancies6 + CountVacancies7 + CountVacancies8 + CountVacancies9 + CountVacancies10 AS TotalCount " & _
  	"FROM (SELECT SUM(CASE WHEN PositionStatus1 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies1, " 
               "SUM(CASE WHEN PositionStatus2 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies2, " _ & 
               "SUM(CASE WHEN PositionStatus3 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies3, " _ & 
               "SUM(CASE WHEN PositionStatus4 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies4, " _ & 
               "SUM(CASE WHEN PositionStatus5 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies5, " _ & 
               "SUM(CASE WHEN PositionStatus6 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies6, " _ & 
               "SUM(CASE WHEN PositionStatus7 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies7, " _ & 
               "SUM(CASE WHEN PositionStatus8 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies8, " _ & 
               "SUM(CASE WHEN PositionStatus9 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies9, " _ & 
               "SUM(CASE WHEN PositionStatus10 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies10) " _ & 
          "FROM tblStaff) as CountVacanciesTotal "

Open in new window

0
 

Author Comment

by:JLohman
ID: 35098838
Code is generating the following error:
   Microsoft VBScript compilation error '800a0400'
   Expected statement
   "SUM(CASE WHEN PositionStatus2 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies2, " _ &
   ^

Your line 4
0
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35100886
I believe that is because i was missing one of the _ & symbols.

sql CountVacanciesTotal = "SELECT  " & _ 
      "CountVacancies1 + CountVacancies2 + CountVacancies3 + CountVacancies4 + CountVacancies5 + CountVacancies6 + CountVacancies7 + CountVacancies8 + CountVacancies9 + CountVacancies10 AS TotalCount " & _
  	"FROM (SELECT SUM(CASE WHEN PositionStatus1 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies1, "  _ &
               "SUM(CASE WHEN PositionStatus2 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies2, " _ & 
               "SUM(CASE WHEN PositionStatus3 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies3, " _ & 
               "SUM(CASE WHEN PositionStatus4 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies4, " _ & 
               "SUM(CASE WHEN PositionStatus5 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies5, " _ & 
               "SUM(CASE WHEN PositionStatus6 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies6, " _ & 
               "SUM(CASE WHEN PositionStatus7 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies7, " _ & 
               "SUM(CASE WHEN PositionStatus8 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies8, " _ & 
               "SUM(CASE WHEN PositionStatus9 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies9, " _ & 
               "SUM(CASE WHEN PositionStatus10 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies10) " _ & 
          "FROM tblStaff) as CountVacanciesTotal "

Open in new window

0
 

Author Comment

by:JLohman
ID: 35101987
Sorry, I had tried adding the _ &.  Now I am getting the following error:

   Microsoft VBScript compilation error '800a0408'
   Invalid character
   "FROM (SELECT SUM(CASE WHEN PositionStatus1 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies1, " _ &
---------------------------^

I have really studied the code and cannot find the error!

0
 

Author Comment

by:JLohman
ID: 35102181
I changed the _ & to  & _ and that eliminated the one error, but created a TYPE MISMATCH on the following line:

      sql CountVacanciesTotal = "SELECT  " & _

0
 

Author Comment

by:JLohman
ID: 35113705
Does anyone know WHY I am now getting a TYPE MISMATCH?
0
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35113741
try surrounding all the columns that you are adding with parenthesis, i.e. 

 sql CountVacanciesTotal = "SELECT  " & _ 
      "(CountVacancies1 + CountVacancies2 + CountVacancies3 + CountVacancies4 + CountVacancies5 + CountVacancies6 + CountVacancies7 + CountVacancies8 + CountVacancies9 + CountVacancies10) AS TotalCount  " & _
...rest of the query...

Open in new window

0
 

Author Comment

by:JLohman
ID: 35113838
Same error message "TYPE MISMATCH"

occurs on the
     sql CountVacanciesTotal = "SELECT  " & _  
0
 

Author Comment

by:JLohman
ID: 35113903
I found the error for the TYPE MISMATCH (the space after sql)

Code is still generating a SYNTAX ERROR IN THE FROM clause

"FROM (SELECT SUM(CASE WHEN PositionStatus1 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies1, " & _
0
 

Author Comment

by:JLohman
ID: 35113937
If the parenthesis is removed in the FROM Line:

           "FROM SELECT SUM(CASE WHEN PositionStatus1 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies1, " & _

Then an error occurs in:
      objCountVacanciesTotal.Open sqlCountVacanciesTotal, objConn
(Code attached)
'Count the TOTAL NUMBER OF VACANCIES
sqlCountVacanciesTotal = "SELECT " & _ 
      "(CountVacancies1 + CountVacancies2 + CountVacancies3 + CountVacancies4 + CountVacancies5 + CountVacancies6 + CountVacancies7 + CountVacancies8 + CountVacancies9 + CountVacancies10) AS TotalCount " & _
  	"FROM SELECT SUM(CASE WHEN PositionStatus1 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies1, " & _
               "SUM(CASE WHEN PositionStatus2 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies2, " & _ 
               "SUM(CASE WHEN PositionStatus3 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies3, " & _ 
               "SUM(CASE WHEN PositionStatus4 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies4, " & _ 
               "SUM(CASE WHEN PositionStatus5 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies5, " & _ 
               "SUM(CASE WHEN PositionStatus6 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies6, " & _ 
               "SUM(CASE WHEN PositionStatus7 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies7, " & _ 
               "SUM(CASE WHEN PositionStatus8 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies8, " & _ 
               "SUM(CASE WHEN PositionStatus9 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies9, " & _ 
               "SUM(CASE WHEN PositionStatus10 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies10 " & _ 
          "FROM tblStaff) as CountVacanciesTotal "

	Set objCountVacanciesTotal = Server.CreateObject("ADODB.Recordset")
	objCountVacanciesTotal.Open sqlCountVacanciesTotal, objConn

Open in new window

0
 

Author Comment

by:JLohman
ID: 35123139
WHenever one error is corrected, another appears. Could someone please take another look?

THanks.
0
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35130032
I cannot tell why your code is giving you errors.  Here is another option.  though it is very inefficient and i would not suggest it for any type of large scale databse...

Select (temp1.p1 + temp2.p2 + ... + temp10.p10) as Total
from
(SELECT count(*) as p1
FROM tblStaff
WHERE PositionStatus1='VACANT') as temp1, 
(SELECT count(*) as p2
FROM tblStaff
WHERE PositionStatus2='VACANT') as temp2,
...
(SELECT count(*) as p10
FROM tblStaff
WHERE PositionStatus10='VACANT') as temp10

Open in new window


This might fix your problem...
0
 
LVL 4

Assisted Solution

by:Alex Matzinger
Alex Matzinger earned 1600 total points
ID: 35134240
Um...before you try the last suggestion, or give up...check your parenthesis.  

Count the TOTAL NUMBER OF VACANCIES
sqlCountVacanciesTotal = "SELECT " & _
      "(CountVacancies1 + CountVacancies2 + CountVacancies3 + CountVacancies4 + CountVacancies5 + CountVacancies6 + CountVacancies7 + CountVacancies8 + CountVacancies9 + CountVacancies10) AS TotalCount " & _
        "FROM (SELECT SUM(CASE WHEN PositionStatus1 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies1, " & _
               "SUM(CASE WHEN PositionStatus2 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies2, " & _
               "SUM(CASE WHEN PositionStatus3 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies3, " & _
               "SUM(CASE WHEN PositionStatus4 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies4, " & _
               "SUM(CASE WHEN PositionStatus5 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies5, " & _
               "SUM(CASE WHEN PositionStatus6 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies6, " & _
               "SUM(CASE WHEN PositionStatus7 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies7, " & _
               "SUM(CASE WHEN PositionStatus8 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies8, " & _
               "SUM(CASE WHEN PositionStatus9 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies9, " & _
               "SUM(CASE WHEN PositionStatus10 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies10 " & _
          "FROM tblStaff) as CountVacanciesTotal "

      Set objCountVacanciesTotal = Server.CreateObject("ADODB.Recordset")
      objCountVacanciesTotal.Open sqlCountVacanciesTotal, objConn
0
 

Accepted Solution

by:
JLohman earned 0 total points
ID: 35158449
I found a solution using UNION ALL:

sqlCountVacanciesTotal = "select sum(CountVacancies) as CountVacanciesTotal from (SELECT count(PositionStatus1) as CountVacancies FROM tblStaff where PositionStatus1 = 'Vacant' " & _
"union all SELECT count(PositionStatus2) as CountVacancies FROM tblStaff where PositionStatus2 = 'Vacant' " & _
"union all SELECT count(PositionStatus3) as CountVacancies FROM tblStaff where PositionStatus3 = 'Vacant' " & _
"union all SELECT count(PositionStatus4) as CountVacancies FROM tblStaff where PositionStatus4 = 'Vacant' " & _
"union all SELECT count(PositionStatus5) as CountVacancies FROM tblStaff where PositionStatus5 = 'Vacant' " & _
"union all SELECT count(PositionStatus6) as CountVacancies FROM tblStaff where PositionStatus6 = 'Vacant' " & _
"union all SELECT count(PositionStatus7) as CountVacancies FROM tblStaff where PositionStatus7 = 'Vacant' " & _
"union all SELECT count(PositionStatus8) as CountVacancies FROM tblStaff where PositionStatus8 = 'Vacant' " & _
"union all SELECT count(PositionStatus9) as CountVacancies FROM tblStaff where PositionStatus9 = 'Vacant' " & _
"union all SELECT count(PositionStatus10) as CountVacancies FROM tblStaff where PositionStatus10 = 'Vacant')"
0
 

Author Comment

by:JLohman
ID: 35159964
Support appreciated.
0
 

Author Closing Comment

by:JLohman
ID: 35187438
Solution was never reached;
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Learn More
Question has a verified solution.

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

Automating Microsoft Excel from Access (Part II)
Article by: Dale Fye
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Delivering a Word Template for Use in Merging Data from Access
Article by: Helen
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
MS Access – Cascading Combos
Video by: Jim
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Create a Query and Grouped Report and Modify Design using Access
Video by: crystal
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Advertise Here

688 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

Advertise Here