SUM of data for ONE record - Multiple fields

JLohman
JLohman used Ask the Experts™
on
I am using
- MSAccess
- Microsoft SQL Server  2000 - 8.00.760 (Intel X86)
- FrontPage 2003

I am using two table:  tblGeneral and tblStaff. Common field joining data is ChartID

Fields I am using are:
Title 1, Title2, Title3, Title4, Title5, Title 6, Title7, Title8, Title9, Title10
PositionStatus1, PositionStatus2, PositionStatus3, PositionStatus4, PositionStatus5,
   PositionStatus6, PositionStatus7, PositionStatus8, PositionStatus9, PositionStatus10
FTE1, FTE2, FTE3, FTE4, FTE5, FTE6, FTE7, FTE8, FTE9, FTE10

I want to add the total of the data in the following fields:
FTE1+FTE2+ FTE3+FTE4+FTE5+FTE6+FTE7+FTE8+FTE9+FTE10
based on the criteria that :
- Title contains the string Techn AND
- PositionStatus = Filled AND
- ChartID = ChartID

Example of ONE record is:
Position1
Title: Library Technician
PositionStatus:  Filled
FTE: 1.0

Position2
Title: Supervisory Technician
PositionStatus:  Filled
FTE: .5

Position3
Title: Secretary
PositionStatus:  Filled
FTE: 1.0

Result should be 1.5

I am attaching my SQL code. The first part of the code (SELECT SUM) does not add the contents of fields FTE1 + FTE2 + FTE3....

The area of the code count (1) is just counting the number of times where the criteria is met. I now need sums for the contents of the FTE[1-10] fields.
sql2 = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, tblGeneral.Complexity, " & _
 		"tblGeneral.Facility, t_staff.LibFTE, t_staff.OnBoardFTE, t_staff.StaffGenNotes, " & _
 		"t_staff.Title1, t_staff.Title2, t_staff.Title3, t_staff.Title4, t_staff.Title5, " & _
 		"t_staff.Title6, t_staff.Title7, t_staff.Title8, t_staff.Title9, t_staff.Title10, " & _
 		"t_staff.FTE1, t_staff.FTE2, t_staff.FTE3, t_staff.FTE4, t_staff.FTE5, " & _
 		"t_staff.FTE6, t_staff.FTE7, t_staff.FTE8, t_staff.FTE9, t_staff.FTE10, " & _
 		"t_staff.PositionStatus1, t_staff.PositionStatus2, t_staff.PositionStatus3, " & _
 		"t_staff.PositionStatus4, t_staff.PositionStatus5, t_staff.PositionStatus6, " & _
 		"t_staff.PositionStatus7, t_staff.PositionStatus8, t_staff.PositionStatus9, t_staff.PositionStatus10, " & _
 		"( " & _
		"(select SUM(FTE1) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus1 = 'Filled' AND ChartID=t_staff.ChartID) + " & _
		"(select SUM(FTE2) from tblStaff where Title2 LIKE '%Techn%' and PositionStatus2 = 'Filled' AND ChartID=t_staff.ChartID) +  " & _
		"(select SUM(FTE3) from tblStaff where Title3 LIKE '%Techn%' and PositionStatus3 = 'Filled' AND ChartID=t_staff.ChartID) + " & _
		"(select SUM(FTE4) from tblStaff where Title4 LIKE '%Techn%' and PositionStatus4 = 'Filled' AND ChartID=t_staff.ChartID) + " & _
		"(select SUM(FTE5) from tblStaff where Title5 LIKE '%Techn%' and PositionStatus5 = 'Filled' AND ChartID=t_staff.ChartID) + " & _
		"(select SUM(FTE6) from tblStaff where Title6 LIKE '%Techn%' and PositionStatus6 = 'Filled' AND ChartID=t_staff.ChartID) + " & _
		"(select SUM(FTE7) from tblStaff where Title7 LIKE '%Techn%' and PositionStatus7 = 'Filled' AND ChartID=t_staff.ChartID) + " & _
		"(select SUM(FTE8) from tblStaff where Title8 LIKE '%Techn%' and PositionStatus8 = 'Filled' AND ChartID=t_staff.ChartID) + " & _
		"(select SUM(FTE9) from tblStaff where Title9 LIKE '%Techn%' and PositionStatus9 = 'Filled' AND ChartID=t_staff.ChartID) + " & _
		"(select SUM(FTE10) from tblStaff where Title10 LIKE '%Techn%' and PositionStatus10 = 'Filled' AND ChartID=t_staff.ChartID) " & _ 
		") AS SUMTechsFilled, " & _
		"( " & _
		"(select count(1) from tblStaff where Title1 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title2 LIKE '%Librarian%' and ChartID=t_staff.ChartID) +  " & _
		"(select count(1) from tblStaff where Title3 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title4 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title5 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title6 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title7 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title8 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title9 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title10 LIKE '%Librarian%' and ChartID=t_staff.ChartID) " & _ 
		") AS cntLibrary, " & _
		"( " & _
		"(select count(1) from tblStaff where PositionStatus1 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus2 LIKE '%Vacant%' and ChartID=t_staff.ChartID) +  " & _
		"(select count(1) from tblStaff where PositionStatus3 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus4 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus5 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus6 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus7 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus8 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus9 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus10 LIKE '%Vacant%' and ChartID=t_staff.ChartID) " & _ 
		") AS cntVacant, " & _
		"( " & _
		"(select count(1) from tblStaff where (Title1 NOT LIKE '%Librarian%') and (Title1 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title2 NOT LIKE '%Librarian%') and (Title2 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) +  " & _
		"(select count(1) from tblStaff where (Title3 NOT LIKE '%Librarian%') and (Title3 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title4 NOT LIKE '%Librarian%') and (Title4 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title5 NOT LIKE '%Librarian%') and (Title5 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title6 NOT LIKE '%Librarian%') and (Title6 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title7 NOT LIKE '%Librarian%') and (Title7 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title8 NOT LIKE '%Librarian%') and (Title8 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title9 NOT LIKE '%Librarian%') and (Title9 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title10 NOT LIKE '%Librarian%') and (Title10 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) " & _ 
		") AS cntOther " & _
		"FROM tblGeneral INNER JOIN tblStaff t_staff ON tblGeneral.ChartID= t_staff.ChartID "

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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale BurrellDirector

Commented:
I don't have time to give you a specific solution, so here is the general one and maybe you are able to apply this to your specific situation.

select
  sum(case when Title like '%tech%' and PositionStatus like '%filled%' then Field1 + Field2 else 0 end)
from MyComplexQuery

Obviously the condition can be improved as can the fields to be summed.

Hope that gives you a starting point.

Of course it also depends on where you are using SQL Server 2005 or Access as they are quite different.

Author

Commented:
I don't want the SUM of a case.... I need the sum of the data in the 10 FTE fields based on the criteria.
Dale BurrellDirector

Commented:
Well summing a case does what you describe? It sums the 10 fields based on the criteria...
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Aaron TomoskyDirector of Solutions Consulting

Commented:
In SQL you only sum rows. To add columns together you just use the plus sign. If the data is in a varchar field then you have to: cast(fieldname as decimal(6,2)) or something that's a number or it will just combine it like a string instead of adding numbers.

Author

Commented:
My database is MSAccess.
I want to get the sum of
FTE1 + FTE2 + FTE3 + FTE4 + FTE5 + FTE6 + FTE7 + FTE8 + FTE9 + FTE10
(example 1 + .5 + 1 + .33 + 0 = 2.83)
These fields are ALL in ONE record. The data in this field is a number.
Aaron TomoskyDirector of Solutions Consulting

Commented:
Select title, FTE1 + FTE2 + FTE3 + FTE4 + FTE5 + FTE6 + FTE7 + FTE8 + FTE9 + FTE10 as addedstuff
From tablename

You may have to get rid of all the extra spaces in there For it to run.

Author

Commented:
I need the sum of
FTE1 where Title1 LIKE '%Techn%' and PositionStatus1 = 'Filled' AND ChartID=t_staff.ChartID) +
FTE2 where Title2 LIKE '%Techn%' and PositionStatus2 = 'Filled' AND ChartID=t_staff.ChartID) +
FTE3 where Title3 LIKE '%Techn%' and PositionStatus3 = 'Filled' AND ChartID=t_staff.ChartID) +
.....
FTE10 where Title10 LIKE '%Techn%' and PositionStatus10 = 'Filled' AND ChartID=t_staff.ChartID)
I hope it should work, just apply the cast to your query:

e.g.
"cast((select SUM(FTE1) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus1 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
            "cast((select SUM(FTE2) from tblStaff where Title2 LIKE '%Techn%' and PositionStatus2 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) +  " & _
....................................
so on....

Let me if you have any difficulty.

Author

Commented:
I am getting the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 't_staff.PositionStatus10 ( (cast((select SUM(FTE1) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus1 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + cast((select SUM(FTE2) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus2 '.




Code is attached.
sql2 = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, tblGeneral.Complexity, " & _
 		"tblGeneral.Facility, t_staff.LibFTE, t_staff.OnBoardFTE, t_staff.StaffGenNotes, " & _
 		"t_staff.Title1, t_staff.Title2, t_staff.Title3, t_staff.Title4, t_staff.Title5, " & _
 		"t_staff.Title6, t_staff.Title7, t_staff.Title8, t_staff.Title9, t_staff.Title10, " & _
 		"t_staff.FTE1, t_staff.FTE2, t_staff.FTE3, t_staff.FTE4, t_staff.FTE5, " & _
 		"t_staff.FTE6, t_staff.FTE7, t_staff.FTE8, t_staff.FTE9, t_staff.FTE10, " & _
 		"t_staff.PositionStatus1, t_staff.PositionStatus2, t_staff.PositionStatus3, " & _
 		"t_staff.PositionStatus4, t_staff.PositionStatus5, t_staff.PositionStatus6, " & _
 		"t_staff.PositionStatus7, t_staff.PositionStatus8, t_staff.PositionStatus9, t_staff.PositionStatus10 " & _
 		"( " & _
		"cast((select SUM(FTE1) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus1 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE2) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus2 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE3) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus3 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE4) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus4 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE5) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus5 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE6) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus6 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE7) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus7 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE8) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus8 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE9) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus9 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE10) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus10 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2) " & _
		") AS SUMTechsFilled, " & _
		"( " & _
		"(select count(1) from tblStaff where Title1 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + "

Open in new window

Replace your above code with this one!

I have found and updated it, try it:
sql2 = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, tblGeneral.Complexity, " & _
 		"tblGeneral.Facility, t_staff.LibFTE, t_staff.OnBoardFTE, t_staff.StaffGenNotes, " & _
 		"t_staff.Title1, t_staff.Title2, t_staff.Title3, t_staff.Title4, t_staff.Title5, " & _
 		"t_staff.Title6, t_staff.Title7, t_staff.Title8, t_staff.Title9, t_staff.Title10, " & _
 		"t_staff.FTE1, t_staff.FTE2, t_staff.FTE3, t_staff.FTE4, t_staff.FTE5, " & _
 		"t_staff.FTE6, t_staff.FTE7, t_staff.FTE8, t_staff.FTE9, t_staff.FTE10, " & _
 		"t_staff.PositionStatus1, t_staff.PositionStatus2, t_staff.PositionStatus3, " & _
 		"t_staff.PositionStatus4, t_staff.PositionStatus5, t_staff.PositionStatus6, " & _
 		"t_staff.PositionStatus7, t_staff.PositionStatus8, t_staff.PositionStatus9, t_staff.PositionStatus10, " & _
 		"( " & _
		"cast((select SUM(FTE1) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus1 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE2) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus2 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE3) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus3 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE4) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus4 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE5) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus5 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE6) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus6 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE7) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus7 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE8) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus8 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE9) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus9 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE10) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus10 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) " & _
		") AS SUMTechsFilled, " & _
		"( " & _
		"(select count(1) from tblStaff where Title1 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + "

Open in new window

G_H

Commented:
Hi,

I usually find if the answer to a question is getting too long, then it maybe easier / more maintainable / more sensible to look at a different way of doing it.

Since you are talking about a single record, why not collect that record, and process the result with asp:

sql = "SELECT Appropriate Fields, FROM appropriate Table WHERE Appropriate Condition
rs = Conn.Execute(sql)
...
decimalResult = CSng(rs("FTE1")) + CSng(rs("FTE2")) + CSng(rs("FTE3")) + .....

Would this not be easier?

GH

Author

Commented:
prajapati84:  I was having a web server problem and could not get back to you sooner. I am still getting the following error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '( cast((select SUM(FTE1) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus1 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + cast((select SUM(FTE2) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus2 = 'Filled' AND ChartID=t_s'.


G H: The problem with the solution you recommended is I need the conditions met for each of the FTE[1-10] fields
Hi JLohman

My above query works fine in my environment.

for your error,
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
Pleaser refer: http://support.microsoft.com/kb/223758

Author

Commented:
I carefully read the reference from microsoft support. This explanation is not related to my error message. I am getting "Syntax error (missing operator) in query expression".
Can you give me your full query, so that i can correct.

Author

Commented:
Thank you for your persistance. This has been very difficult to resolve. I am attaching my code.
<!doctype html public "-//w3c//dtd html 4.0 strict//en">
<!-- #Include Virtual="/CodeReuse/ConnComparisonChart.inc" -->


<%
	dim title
	title = "Comparison Chart. Staffing (FTE and On-Board) Overview"
%>


<%

'Create recordset
 Dim sql2, objRS2, counter
 counter = 0


sql2 = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, tblGeneral.Complexity, " & _
 		"tblGeneral.Facility, t_staff.LibFTE, t_staff.OnBoardFTE, t_staff.StaffGenNotes, " & _
 		"t_staff.Title1, t_staff.Title2, t_staff.Title3, t_staff.Title4, t_staff.Title5, " & _
 		"t_staff.Title6, t_staff.Title7, t_staff.Title8, t_staff.Title9, t_staff.Title10, " & _
 		"t_staff.FTE1, t_staff.FTE2, t_staff.FTE3, t_staff.FTE4, t_staff.FTE5, " & _
 		"t_staff.FTE6, t_staff.FTE7, t_staff.FTE8, t_staff.FTE9, t_staff.FTE10, " & _
 		"t_staff.PositionStatus1, t_staff.PositionStatus2, t_staff.PositionStatus3, " & _
 		"t_staff.PositionStatus4, t_staff.PositionStatus5, t_staff.PositionStatus6, " & _
 		"t_staff.PositionStatus7, t_staff.PositionStatus8, t_staff.PositionStatus9, t_staff.PositionStatus10, " & _
 		"( " & _
		"cast((select SUM(FTE1) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus1 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE2) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus2 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE3) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus3 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE4) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus4 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE5) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus5 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE6) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus6 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE7) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus7 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE8) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus8 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE9) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus9 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE10) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus10 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) " & _
		") AS SUMTechsFilled, " & _
		"( " & _
		"(select count(1) from tblStaff where Title1 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title2 LIKE '%Librarian%' and ChartID=t_staff.ChartID) +  " & _
		"(select count(1) from tblStaff where Title3 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title4 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title5 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title6 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title7 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title8 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title9 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title10 LIKE '%Librarian%' and ChartID=t_staff.ChartID) " & _ 
		") AS cntLibrary, " & _
		"( " & _
		"(select count(1) from tblStaff where PositionStatus1 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus2 LIKE '%Vacant%' and ChartID=t_staff.ChartID) +  " & _
		"(select count(1) from tblStaff where PositionStatus3 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus4 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus5 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus6 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus7 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus8 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus9 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus10 LIKE '%Vacant%' and ChartID=t_staff.ChartID) " & _ 
		") AS cntVacant, " & _
		"( " & _
		"(select count(1) from tblStaff where (Title1 NOT LIKE '%Librarian%') and (Title1 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title2 NOT LIKE '%Librarian%') and (Title2 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) +  " & _
		"(select count(1) from tblStaff where (Title3 NOT LIKE '%Librarian%') and (Title3 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title4 NOT LIKE '%Librarian%') and (Title4 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title5 NOT LIKE '%Librarian%') and (Title5 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title6 NOT LIKE '%Librarian%') and (Title6 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title7 NOT LIKE '%Librarian%') and (Title7 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title8 NOT LIKE '%Librarian%') and (Title8 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title9 NOT LIKE '%Librarian%') and (Title9 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title10 NOT LIKE '%Librarian%') and (Title10 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) " & _ 
		") AS cntOther " & _
		"FROM tblGeneral INNER JOIN tblStaff t_staff ON tblGeneral.ChartID= t_staff.ChartID "

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



'------------------------------------------
'COUNT NUMBER OF FACILITIES REPORTING DATA
'------------------------------------------
	sqlCountChartID = "SELECT COUNT(tblStaff.ChartID) AS CountChartID " & _
	"FROM tblStaff " 

	Set objCountChartID = Server.CreateObject("ADODB.Recordset")
	objCountChartID.Open sqlCountChartID, objConn


'------------------------------------------
'CALCULATIONS FOR FTE 
'------------------------------------------
	sqlSumLibFTE = "SELECT Sum(tblStaff.LibFTE) AS SumLibFTE " & _
	"FROM tblStaff " 

	Set objSumLibFTE = Server.CreateObject("ADODB.Recordset")
	objSumLibFTE.Open sqlSumLibFTE, objConn


'The following includes the records with 0 responses and then calculates an average
	sqlAvgLibFTE = "SELECT SUM(LibFTE)/COUNT(LibFTE) AS AvgLibFTE " & _
	"FROM tblStaff " 

	Set objAvgLibFTE = Server.CreateObject("ADODB.Recordset")
	objAvgLibFTE.Open sqlAvgLibFTE, objConn



'------------------------------------------
'CALCULATION FOR OnBoardFTE 
'------------------------------------------
	sqlSumOnBoardFTE = "SELECT Sum(tblStaff.OnBoardFTE) AS SumOnBoardFTE " & _
	"FROM tblStaff " 

	Set objSumOnBoardFTE = Server.CreateObject("ADODB.Recordset")
	objSumOnBoardFTE.Open sqlSumOnBoardFTE, objConn


	sqlAvgOnBoardFTE = "SELECT SUM(OnBoardFTE)/COUNT(OnBoardFTE) AS AvgOnBoardFTE " & _
  	"FROM tblStaff "

	Set objAvgOnBoardFTE = Server.CreateObject("ADODB.Recordset")
	objAvgOnBoardFTE.Open sqlAvgOnBoardFTE, objConn


'--------------------------------
'Percentage of FILLED positions
'--------------------------------

	sqlFilled = "SELECT SUM(OnBoardFTE) / SUM(LibFTE) * 100 AS Filled " & _
	"FROM tblStaff " 

	Set objFilled = Server.CreateObject("ADODB.Recordset")
	objFilled.Open sqlFilled, objConn


'--------------------------------
'No of VACANCIES
'--------------------------------

	sqlVacancies = "SELECT SUM(LibFTE) - SUM(OnBoardFTE) AS Vacancies " & _
	"FROM tblStaff " 

	Set objVacancies = Server.CreateObject("ADODB.Recordset")
	objVacancies.Open sqlVacancies, objConn




if err.number <> 0 then
  response.write err.description
end if


%>


<html>

<head>
	<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
	<meta http-equiv="Subject" content="Library Orientation Evaluation">
	<meta http-equiv="Keywords" content="Data Collection, Department of Veterans Affairs, Evaluation Studies, Inservice Training, Libraries, Library Orientation, Library Services, Organization and Administration, Personnel Management, Questionnaires, VA Library Network, VALNET">
	<title><%=title%></title>

	<link rel=stylesheet type="text/css"
	href="/VALNET/CodeReuse/style.css">
</head>


<body>
	
<h2 align="left"><font face="Arial" size="4"><%=title%></font></h2>


<%
	Response.Write "<ul>"

	Response.Write "<li><font face='Arial' font size=2'>Total number of facilities reporting data:  "
  	Response.write (objCountChartID("CountChartID")) 
	Response.Write "</li>"

	Response.Write "<li><font face='Arial' font size=2'>TOTAL Approved FTE Staffing:  "
	Response.write Formatnumber(objSumLibFTE("SumLibFTE"),2) 
	Response.write "<i>&nbsp;(" & Formatnumber(objFilled("Filled"),0) & "% filled)</i>"

	Response.Write "<li><font face='Arial' font size=2'>Average Approved FTE Staffing Level:  "
	Response.write Formatnumber(objAvgLibFTE("AvgLibFTE"),2) 
	Response.Write "</li>"

	Response.Write "<li><font face='Arial' font size=2'>TOTAL Actual (on-board) FTE Staffing:  "
	Response.write Formatnumber(objSumOnBoardFTE("SumOnBoardFTE"),2) 

	Response.Write "<li><font face='Arial' font size=2'>Average Actual (on-board) FTE Staffing Level:  "
  	Response.write Formatnumber(objAvgOnBoardFTE("AvgOnBoardFTE"),2) 

	Response.Write "<li><font face='Arial' font size=2'>TOTAL number of Vacancies:  "
  	Response.write Formatnumber(objVacancies("Vacancies"),2) 

	Response.Write "</li></ul>"

	Response.Write "<hr>"
%>

* Indicates a consolidated facility, no separate complexity level assigned.<br>
# A count of the number of positions in this category (not FTE) 

<%

	'First, display the TABLE header info:
	Response.Write "<TABLE class='CCTable' cellspacing='0' border='1' cellpadding='2' width='1000px'>"

	'Display table headings for each column in the Recordset

	Response.Write "<TR style='position:absolute;top:264px;background-color:white;'>"
	Response.Write "<TH class='CCTable' scope='col' width='25px'>&nbsp;&nbsp;</th>"
	Response.Write "<TH class='CCTable' scope='col' width='172px'><a href='StaffingPositions.asp?sort=City'><font size='1' color='blue'><b>Location</b></a></font></th></th>"
	Response.Write "<TH class='CCTable' scope='col' width='85px'><a href='StaffingPositions.asp?sort=Facility'><font size='1' color='blue'><b>Facility</b></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='50px'><a href='StaffingPositions.asp?sort=VISNID'><font size='1' color='blue'><b>VISN</b></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='75px'><a href='StaffingPositions.asp?sort=Complexity'><font size='1' color='blue'><b>Complexity</a> *</b><br><a target='_blank' href='/Valnet/ComparisonChart/ComplexityLevels.htm'><font size='1'>What is this?</a></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='70px'><a href='StaffingPositions.asp?sort=LibFTE'><font size='1' color='blue'><b>Approved<br>Library<br>FTE</b></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='72px'><font size='1' color='blue'><b>No. of<br>Librarian<br>Positions #</b></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='75px'><font size='1' color='blue'><b>No. of<br>Tech<br>Positions #</b></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='75px'><font size='1' color='blue'><b>No. of<br>All Other<br>Titles #</b></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='75px'><a href='StaffingPositions.asp?sort=OnBoardFTE'><font size='1' color='blue'><b>Actual<br>Library<br>FTE</b></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='75px'><font size='1' color='blue'><b>No. of<br>Vacancies #</b></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='250px'><font size='1' color='blue'><b>General<br>Staff<br>Notes</b></font></th></tr></TABLE>"

	Response.Write "<div style='OVERFLOW:auto; HEIGHT:400px; position:absolute;top:308px;width:1025px'>"
	Response.Write "<TABLE class='CCTable' cellspacing='0' border='1' cellpadding='0' width='1000px'>"


	While Not objRS2.EOF
	counter = counter + 1
		Response.Write "<TR><TD class='CCTable' scope='row' width='25px'>" & counter
		Response.Write "<TD class='CCTable' scope='row' width='148px'>" & objRS2("City") & ", " & objRS2("State") & "<font color='#FF0000'><b>" & "</FONT></B></TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("Facility") & "&nbsp;</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='50px'>" & objRS2("VISNID") & "&nbsp;</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='80px'>" & objRS2("Complexity") & "&nbsp;</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("LibFTE") & "&nbsp;</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("cntLibrary") & "</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("SumTechsFilled") & "</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("cntOther") & "</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("OnBoardFTE") & "&nbsp;</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("cntVacant") & "&nbsp;</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='218px'>" & objRS2("StaffGenNotes") & "&nbsp;</TD>" & VbCrLf

   		'Advance the recordset
   		objRS2.MoveNext
	Wend
	
	objRS2.Close
	Set objRS2 = Nothing

	Response.Write "</td></TABLE>" & vbCrLf
	Response.Write "</div>"

%>



</body>
</html>

Open in new window

G_H

Commented:
Hi Again,

Sorry to keep beating the same point, but looking at the length of the SQL alone, I would recommend trying my way. At least consider:

 
Dim sql, rs, i, RowCounter, HitCounter

'' ## Loop 10 times to get the 
for i = 1 to 10
	sql = sql & "(Title" & i & " LIKE '%Techn%' AND PersonStatus='Filled') OR "
next
'' ## Trim the last "OR"
sql = Left(sql, len(sql)-4)
'' ## Complete the sql Statement
sql = "SELECT Fields FROM Tables WHERE (" & sql & ") AND ChartID=t_staff.ChartID"
'' ## Collect the likely data
Set rs = Conn.execute(sql)
	'' ## check we have a result at all
	if NOT(rs.EOF) then
		while NOT(rs.EOF)
		RowCounter = RowCounter + 1
			'' ## Check every one from 1 to 10
			for i = 1 to 10
				'' ## Only do the adding if it maches our type
				if InStr(rs("Title" & i), "Techn") then
					TheSum = TheSum + rs("FTE" & i)
					HitCounter = HitCounter + 1
				end if
			next
			rs.movenext
		wend
	end if
	rs.close
Set rs = nothing

'' ## The variable TheSum will now have the value 1.5, as per the original question.

Open in new window


The result at the end "TheSum" will do exactly as you requested in your question (1.5).

Obviously, this will require some tweaking, to fit your application, but I think you are bright enough for that.

GH
Commented:
I want to add the data in the fields (FTE{1-10]) not just count.

Example of record (ChartID 1):
FTE1 (.5)  -  Title1(Secretary)  -  PostionStatus1(Filled)
FTE2 (1.0)  -  Title2(Technician)  -  PostionStatus1(Filled)
FTE3 (1.0)  -  Title3(Technical Assistant)  -  PostionStatus1(Filled)
FTE4 (1.0)  -  Title4(Assistant)  -  PostionStatus1(Vacant)
FTE5 (.5)  -  Title5(Supervisory Technician)  -  PostionStatus1(Filled)
FTE6 (.25)  -  Title6(Clerk)  -  PostionStatus1(Filled)
FTE7 (1.0)  -  Title7(Technician)  -  PostionStatus1(Filled)
FTE8 (.75)  -  Title8(Supervisor)  -  PostionStatus1(Filled)
FTE9 (1)  -  Title9(Secretary)  -  PostionStatus1(Filled)
FTE10 (.5)  -  Title10(Secretary)  -  PostionStatus1(Vacant)

Example of record (ChartID 2):
FTE1 (.5)  -  Title1(Clerk)  -  PostionStatus1(Filled)
FTE2 (1.0)  -  Title2(Technician)  -  PostionStatus1(Filled)
FTE3 (1.0)  -  Title3(Technical Assistant)  -  PostionStatus1(Filled)
FTE4 (1.0)  -  Title4(Assistant)  -  PostionStatus1(Vacant)
FTE5 (.5)  -  Title5(Supervisory Technician)  -  PostionStatus1(Filled)

Example of record (ChartID 3):
FTE1 (1.0)  -  Title1(Supervisory Technician)  -  PostionStatus1(Filled)
FTE2 (.5)  -  Title2(Clerk)  -  PostionStatus1(Filled)
FTE3 (1.0)  -  Title3(Technical Assistant)  -  PostionStatus1(Filled)
FTE4 (1.0)  -  Title4(Assistant)  -  PostionStatus1(Vacant)

Display results:

ChartID     FTE of Tech positions filled
1                       3.5
2                       2.5
3                       2.0
G_H
Commented:
OK, then slightly modify the code:

 
Dim sql, rs, i, RowCounter, HitCounter, TheSum

'' ## Loop 10 times to get the 
for i = 1 to 10
	sql = sql & "(Title" & i & " LIKE '%Techn%' AND PersonStatus='Filled') OR "
next
'' ## Trim the last "OR"
sql = Left(sql, len(sql)-4)
'' ## Complete the sql Statement
sql = "SELECT Fields, ChartID FROM Tables WHERE (" & sql & ") AND ChartID=t_staff.ChartID"
'' ## Collect the likely data
Set rs = Conn.execute(sql)
	'' ## check we have a result at all
	if NOT(rs.EOF) then
		response.write "ChartID / FTE of Tech positions Filled
		while NOT(rs.EOF)
			TheSum = 0
			RowCounter = RowCounter + 1
			'' ## Check every one from 1 to 10
			for i = 1 to 10
				'' ## Only do the adding if it maches our type
				if InStr(rs("Title" & i), "Techn") then
					TheSum = TheSum + rs("FTE" & i)
					HitCounter = HitCounter + 1
				end if
			next
			response.write rs("ChartID") & " = " & TheSum & "<br />"
			rs.movenext
		wend
	end if
	rs.close
Set rs = nothing

Open in new window


GH

Author

Commented:
Sorry for not responding. Computer network problem with FrontPage.

I understand what you are doing with the looping. I am not sure what your code is replacing in my original code. I eventually want to be able to substitue the new code for each section of the original sql statement (ie,
     count(1) from tblStaff where Title1 LIKE '%Librarian%'
     count(1) from tblStaff where PositionStatus1 LIKE '%Vacant%'
     count(1) from tblStaff where (Title1 NOT LIKE '%Librarian%') and (Title1 NOT LIKE '%Tech%')

I'm beginning to think this project is beyond me!

<%

'Create recordset
 Dim sql2, objRS2
 

sql2 = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, tblGeneral.Complexity, " & _
 		"tblGeneral.Facility, t_staff.LibFTE, t_staff.OnBoardFTE, t_staff.StaffGenNotes, " & _
 		"t_staff.Title1, t_staff.Title2, t_staff.Title3, t_staff.Title4, t_staff.Title5, " & _
 		"t_staff.Title6, t_staff.Title7, t_staff.Title8, t_staff.Title9, t_staff.Title10, " & _
 		"t_staff.FTE1, t_staff.FTE2, t_staff.FTE3, t_staff.FTE4, t_staff.FTE5, " & _
 		"t_staff.FTE6, t_staff.FTE7, t_staff.FTE8, t_staff.FTE9, t_staff.FTE10, " & _
 		"t_staff.PositionStatus1, t_staff.PositionStatus2, t_staff.PositionStatus3, " & _
 		"t_staff.PositionStatus4, t_staff.PositionStatus5, t_staff.PositionStatus6, " & _
 		"t_staff.PositionStatus7, t_staff.PositionStatus8, t_staff.PositionStatus9, t_staff.PositionStatus10, " & _
 		"( " & _
		"cast((select SUM(FTE1) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus1 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE2) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus2 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE3) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus3 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE4) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus4 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE5) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus5 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE6) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus6 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE7) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus7 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE8) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus8 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE9) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus9 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
		"cast((select SUM(FTE10) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus10 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) " & _
		") AS SUMTechsFilled, " & _
		"( " & _
		"(select count(1) from tblStaff where Title1 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + "
		"(select count(1) from tblStaff where Title2 LIKE '%Librarian%' and ChartID=t_staff.ChartID) +  " & _
		"(select count(1) from tblStaff where Title3 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title4 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title5 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title6 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title7 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title8 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title9 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where Title10 LIKE '%Librarian%' and ChartID=t_staff.ChartID) " & _ 
		") AS cntLibrary, " & _
		"( " & _
		"(select count(1) from tblStaff where PositionStatus1 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus2 LIKE '%Vacant%' and ChartID=t_staff.ChartID) +  " & _
		"(select count(1) from tblStaff where PositionStatus3 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus4 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus5 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus6 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus7 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus8 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus9 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where PositionStatus10 LIKE '%Vacant%' and ChartID=t_staff.ChartID) " & _ 
		") AS cntVacant, " & _
		"( " & _
		"(select count(1) from tblStaff where (Title1 NOT LIKE '%Librarian%') and (Title1 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title2 NOT LIKE '%Librarian%') and (Title2 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) +  " & _
		"(select count(1) from tblStaff where (Title3 NOT LIKE '%Librarian%') and (Title3 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title4 NOT LIKE '%Librarian%') and (Title4 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title5 NOT LIKE '%Librarian%') and (Title5 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title6 NOT LIKE '%Librarian%') and (Title6 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title7 NOT LIKE '%Librarian%') and (Title7 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title8 NOT LIKE '%Librarian%') and (Title8 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title9 NOT LIKE '%Librarian%') and (Title9 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
		"(select count(1) from tblStaff where (Title10 NOT LIKE '%Librarian%') and (Title10 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) " & _ 
		") AS cntOther " & _
		"FROM tblGeneral INNER JOIN tblStaff t_staff ON tblGeneral.ChartID= t_staff.ChartID "

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



'------------------------------------------
'COUNT NUMBER OF FACILITIES REPORTING DATA
'------------------------------------------
	sqlCountChartID = "SELECT COUNT(tblStaff.ChartID) AS CountChartID " & _
	"FROM tblStaff " 

	Set objCountChartID = Server.CreateObject("ADODB.Recordset")
	objCountChartID.Open sqlCountChartID, objConn


'------------------------------------------
'CALCULATIONS FOR FTE 
'------------------------------------------
	sqlSumLibFTE = "SELECT Sum(tblStaff.LibFTE) AS SumLibFTE " & _
	"FROM tblStaff " 

	Set objSumLibFTE = Server.CreateObject("ADODB.Recordset")
	objSumLibFTE.Open sqlSumLibFTE, objConn


'The following includes the records with 0 responses and then calculates an average
	sqlAvgLibFTE = "SELECT SUM(LibFTE)/COUNT(LibFTE) AS AvgLibFTE " & _
	"FROM tblStaff " 

	Set objAvgLibFTE = Server.CreateObject("ADODB.Recordset")
	objAvgLibFTE.Open sqlAvgLibFTE, objConn



'------------------------------------------
'CALCULATION FOR OnBoardFTE 
'------------------------------------------
	sqlSumOnBoardFTE = "SELECT Sum(tblStaff.OnBoardFTE) AS SumOnBoardFTE " & _
	"FROM tblStaff " 

	Set objSumOnBoardFTE = Server.CreateObject("ADODB.Recordset")
	objSumOnBoardFTE.Open sqlSumOnBoardFTE, objConn


	sqlAvgOnBoardFTE = "SELECT SUM(OnBoardFTE)/COUNT(OnBoardFTE) AS AvgOnBoardFTE " & _
  	"FROM tblStaff "

	Set objAvgOnBoardFTE = Server.CreateObject("ADODB.Recordset")
	objAvgOnBoardFTE.Open sqlAvgOnBoardFTE, objConn


'--------------------------------
'Percentage of FILLED positions
'--------------------------------

	sqlFilled = "SELECT SUM(OnBoardFTE) / SUM(LibFTE) * 100 AS Filled " & _
	"FROM tblStaff " 

	Set objFilled = Server.CreateObject("ADODB.Recordset")
	objFilled.Open sqlFilled, objConn


'--------------------------------
'No of VACANCIES
'--------------------------------

	sqlVacancies = "SELECT SUM(LibFTE) - SUM(OnBoardFTE) AS Vacancies " & _
	"FROM tblStaff " 

	Set objVacancies = Server.CreateObject("ADODB.Recordset")
	objVacancies.Open sqlVacancies, objConn




if err.number <> 0 then
  response.write err.description
end if


%>


<html>

<head>
	<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
	<meta http-equiv="Subject" content="Library Orientation Evaluation">
	<meta http-equiv="Keywords" content="Data Collection, Department of Veterans Affairs, Evaluation Studies, Inservice Training, Libraries, Library Orientation, Library Services, Organization and Administration, Personnel Management, Questionnaires, VA Library Network, VALNET">
	<title><%=title%></title>

	<link rel=stylesheet type="text/css"
	href="/VALNET/CodeReuse/style.css">
</head>


<body>
	
<h2 align="left"><font face="Arial" size="4"><%=title%></font></h2>


<%
	Response.Write "<ul>"

	Response.Write "<li><font face='Arial' font size=2'>Total number of facilities reporting data:  "
  	Response.write (objCountChartID("CountChartID")) 
	Response.Write "</li>"

	Response.Write "<li><font face='Arial' font size=2'>TOTAL Approved FTE Staffing:  "
	Response.write Formatnumber(objSumLibFTE("SumLibFTE"),2) 
	Response.write "<i>&nbsp;(" & Formatnumber(objFilled("Filled"),0) & "% filled)</i>"

	Response.Write "<li><font face='Arial' font size=2'>Average Approved FTE Staffing Level:  "
	Response.write Formatnumber(objAvgLibFTE("AvgLibFTE"),2) 
	Response.Write "</li>"

	Response.Write "<li><font face='Arial' font size=2'>TOTAL Actual (on-board) FTE Staffing:  "
	Response.write Formatnumber(objSumOnBoardFTE("SumOnBoardFTE"),2) 

	Response.Write "<li><font face='Arial' font size=2'>Average Actual (on-board) FTE Staffing Level:  "
  	Response.write Formatnumber(objAvgOnBoardFTE("AvgOnBoardFTE"),2) 

	Response.Write "<li><font face='Arial' font size=2'>TOTAL number of Vacancies:  "
  	Response.write Formatnumber(objVacancies("Vacancies"),2) 

	Response.Write "</li></ul>"

	Response.Write "<hr>"
%>

* Indicates a consolidated facility, no separate complexity level assigned.<br>
# A count of the number of positions in this category (not FTE) 

<%

	'First, display the TABLE header info:
	Response.Write "<TABLE class='CCTable' cellspacing='0' border='1' cellpadding='2' width='1000px'>"

	'Display table headings for each column in the Recordset

	Response.Write "<TR style='position:absolute;top:264px;background-color:white;'>"
	Response.Write "<TH class='CCTable' scope='col' width='25px'>&nbsp;&nbsp;</th>"
	Response.Write "<TH class='CCTable' scope='col' width='172px'><a href='StaffingPositions.asp?sort=City'><font size='1' color='blue'><b>Location</b></a></font></th></th>"
	Response.Write "<TH class='CCTable' scope='col' width='85px'><a href='StaffingPositions.asp?sort=Facility'><font size='1' color='blue'><b>Facility</b></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='50px'><a href='StaffingPositions.asp?sort=VISNID'><font size='1' color='blue'><b>VISN</b></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='75px'><a href='StaffingPositions.asp?sort=Complexity'><font size='1' color='blue'><b>Complexity</a> *</b><br><a target='_blank' href='/Valnet/ComparisonChart/ComplexityLevels.htm'><font size='1'>What is this?</a></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='70px'><a href='StaffingPositions.asp?sort=LibFTE'><font size='1' color='blue'><b>Approved<br>Library<br>FTE</b></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='72px'><font size='1' color='blue'><b>No. of<br>Librarian<br>Positions #</b></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='75px'><font size='1' color='blue'><b>FTE<br>Tech<br>Positions<br>(Filled)</b></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='75px'><font size='1' color='blue'><b>No. of<br>All Other<br>Titles #</b></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='75px'><a href='StaffingPositions.asp?sort=OnBoardFTE'><font size='1' color='blue'><b>Actual<br>Library<br>FTE</b></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='75px'><font size='1' color='blue'><b>No. of<br>Vacancies #</b></font></th>"
	Response.Write "<TH class='CCTable' scope='col' width='250px'><font size='1' color='blue'><b>General<br>Staff<br>Notes</b></font></th></tr></TABLE>"

	Response.Write "<div style='OVERFLOW:auto; HEIGHT:400px; position:absolute;top:328px;width:1025px'>"
	Response.Write "<TABLE class='CCTable' cellspacing='0' border='1' cellpadding='0' width='1000px'>"


	While Not objRS2.EOF
	counter = counter + 1
		Response.Write "<TR><TD class='CCTable' scope='row' width='25px'>" & counter
		Response.Write "<TD class='CCTable' scope='row' width='148px'>" & objRS2("City") & ", " & objRS2("State") & "<font color='#FF0000'><b>" & "</FONT></B></TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("Facility") & "&nbsp;</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='50px'>" & objRS2("VISNID") & "&nbsp;</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='80px'>" & objRS2("Complexity") & "&nbsp;</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("LibFTE") & "&nbsp;</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("cntLibrary") & "</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("SUMTechsFilled") & "</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("cntOther") & "</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("OnBoardFTE") & "&nbsp;</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("cntVacant") & "&nbsp;</TD>" & VbCrLf
		Response.Write "<TD class='CCTable' scope='row' width='218px'>" & objRS2("StaffGenNotes") & "&nbsp;</TD>" & VbCrLf

   		'Advance the recordset
   		objRS2.MoveNext
	Wend
	
	objRS2.Close
	Set objRS2 = Nothing

	Response.Write "</td></TABLE>" & vbCrLf
	Response.Write "</div>"

%>



</body>
</html>

Open in new window

Author

Commented:
Never was able to complete this code

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial