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!
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+F
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.
- 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+F
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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.
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.
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.
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.
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.
From tablename
You may have to get rid of all the extra spaces in there For it to run.
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)
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.
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.
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.
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) + "
Replace your above code with this one!
I have found and updated it, try it:
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) + "
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
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
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
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
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
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".
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> (" & 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'> </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") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='50px'>" & objRS2("VISNID") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='80px'>" & objRS2("Complexity") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("LibFTE") & " </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") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("cntVacant") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='218px'>" & objRS2("StaffGenNotes") & " </TD>" & VbCrLf
'Advance the recordset
objRS2.MoveNext
Wend
objRS2.Close
Set objRS2 = Nothing
Response.Write "</td></TABLE>" & vbCrLf
Response.Write "</div>"
%>
</body>
</html>
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:
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
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.
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
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
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
OK, then slightly modify the code:
GH
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
GH
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!
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> (" & 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'> </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") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='50px'>" & objRS2("VISNID") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='80px'>" & objRS2("Complexity") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("LibFTE") & " </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") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("cntVacant") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='218px'>" & objRS2("StaffGenNotes") & " </TD>" & VbCrLf
'Advance the recordset
objRS2.MoveNext
Wend
objRS2.Close
Set objRS2 = Nothing
Response.Write "</td></TABLE>" & vbCrLf
Response.Write "</div>"
%>
</body>
</html>
Premium Content
You need an Expert Office subscription to comment.Start Free Trial