Avatar of JLohman
JLohman

asked on 

SUM of data for ONE record - Multiple fields

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

Microsoft SQL Server 2005ASPMicrosoft Access

Avatar of undefined
Last Comment
JLohman
Avatar of Dale Burrell
Dale Burrell
Flag of New Zealand image

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.
Avatar of JLohman
JLohman

ASKER

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.
Avatar of Dale Burrell
Dale Burrell
Flag of New Zealand image

Well summing a case does what you describe? It sums the 10 fields based on the criteria...
Avatar of Aaron Tomosky
Aaron Tomosky
Flag of United States of America image

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.
Avatar of JLohman
JLohman

ASKER

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.
Avatar of Aaron Tomosky
Aaron Tomosky
Flag of United States of America image

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.
Avatar of JLohman
JLohman

ASKER

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)
Avatar of prajapati84
prajapati84
Flag of India image

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.
Avatar of JLohman
JLohman

ASKER

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

SOLUTION
Avatar of prajapati84
prajapati84
Flag of India image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of G_H
G_H
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of JLohman
JLohman

ASKER

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
Avatar of prajapati84
prajapati84
Flag of India image

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
Avatar of JLohman
JLohman

ASKER

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".
Avatar of prajapati84
prajapati84
Flag of India image

Can you give me your full query, so that i can correct.
Avatar of JLohman
JLohman

ASKER

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

Avatar of G_H
G_H
Flag of United Kingdom of Great Britain and Northern Ireland image

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
ASKER CERTIFIED SOLUTION
Avatar of JLohman
JLohman

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
Avatar of G_H
G_H
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of JLohman
JLohman

ASKER

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

Avatar of JLohman
JLohman

ASKER

Never was able to complete this code
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo