Sum of multiple fields in one record based on multiple criteria

I am trying to add data from 10 fields based on 2 criteria to get a total for each record.
I then need to display the totals in a table.

My SQL statement is not correct. It is giving me the following error:


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

[Microsoft][ODBC Microsoft Access Driver] Extra ) in query expression '( (select SUM(FTE1)....

I am attaching code and database.
152Staff.mdb
152Staff.txt
JLohmanAsked:
Who is Participating?
 
aikimarkCommented:
shouldn't cnt1411 be qualified with the recordset name?

I expected something like rs!cnt1411
0
 
joaoalmeidaCommented:
I believe you are missing an "("

Add the "( " &_
after the ") AS cnt1411, " & _

") AS cnt1411, " & _
"( " & _
"(select SUM(FTE1) from tblStaff where Series1 = '1410' AND Pos

Open in new window

0
 
JLohmanAuthor Commented:
Still generates an error:


Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Extra ) in query expression '

 sql = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, " & _
             "tblGeneral.Facility, tblGeneral.VADesignation, tblGeneral.FacilityStatus, " & _
             "tblGeneral.LibraryStatus, " & _
             "tblStaff.OnBoardFTE, " & _
             "tblStaff.Series1, tblStaff.Series2, tblStaff.Series3, tblStaff.Series4, tblStaff.Series5, " & _
             "tblStaff.Series6, tblStaff.Series7, tblStaff.Series8, tblStaff.Series9, tblStaff.Series10, " & _
             "tblStaff.PositionStatus1, tblStaff.PositionStatus2, tblStaff.PositionStatus3, tblStaff.PositionStatus4, tblStaff.PositionStatus5, " & _
             "tblStaff.PositionStatus6, tblStaff.PositionStatus7, tblStaff.PositionStatus8, tblStaff.PositionStatus9, tblStaff.PositionStatus10, " & _
             "tblStaff.FTE1, tblStaff.FTE2, tblStaff.FTE3, tblStaff.FTE4, tblStaff.FTE5, " & _
             "tblStaff.FTE6, tblStaff.FTE7, tblStaff.FTE8, tblStaff.FTE9, tblStaff.FTE10, " & _
             "( " & _
            "(select SUM(FTE1) from tblStaff where Series1 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE2) from tblStaff where Series2 = '1411' AND PositionStatus2 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE3) from tblStaff where Series3 = '1411' AND PositionStatus3 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE4) from tblStaff where Series4 = '1411' AND PositionStatus4 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE5) from tblStaff where Series5 = '1411' AND PositionStatus5 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE6) from tblStaff where Series6 = '1411' AND PositionStatus6 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE7) from tblStaff where Series7 = '1411' AND PositionStatus7 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE8) from tblStaff where Series8 = '1411' AND PositionStatus8 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE9) from tblStaff where Series9 = '1411' AND PositionStatus9 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE10) from tblStaff where Series10 = '1411' AND PositionStatus10 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            ") AS cnt1411, " & _
             "( " & _
          "(select SUM(FTE1) from tblStaff where Series1 = '1410' AND PositionStatus1 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE2) from tblStaff where Series2 = '1410' AND PositionStatus2 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE3) from tblStaff where Series3 = '1410' AND PositionStatus3 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE4) from tblStaff where Series4 = '1410' AND PositionStatus4 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE5) from tblStaff where Series5 = '1410' AND PositionStatus5 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE6) from tblStaff where Series6 = '1410' AND PositionStatus6 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE7) from tblStaff where Series7 = '1410' AND PositionStatus7 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE8) from tblStaff where Series8 = '1410' AND PositionStatus8 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE9) from tblStaff where Series9 = '1410' AND PositionStatus9 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE10) from tblStaff where Series10 = '1410' AND PositionStatus10 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            ") AS cnt1410, " & _

            "( " & _
          "(select SUM(FTE1) from tblStaff where Series1 = 'Other' AND PositionStatus1 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE2) from tblStaff where Series2 = 'Other' AND PositionStatus2 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE3) from tblStaff where Series3 = 'Other' AND PositionStatus3 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE4) from tblStaff where Series4 = 'Other' AND PositionStatus4 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE5) from tblStaff where Series5 = 'Other' AND PositionStatus5 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE6) from tblStaff where Series6 = 'Other' AND PositionStatus6 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE7) from tblStaff where Series7 = 'Other' AND PositionStatus7 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE8) from tblStaff where Series8 = 'Other' AND PositionStatus8 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE9) from tblStaff where Series9 = 'Other' AND PositionStatus9 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE10) from tblStaff where Series10 = 'Other' AND PositionStatus10 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            ") AS cntOther " & _


            "FROM tblGeneral LEFT OUTER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID "
             
             If Request.querystring("sort") = "" then
                   sql = sql & "ORDER BY State, City"
             Else
                    sql = sql & "ORDER BY " & Request.querystring("sort")
              End If                  

      Set objRS = Server.CreateObject("ADODB.Recordset")
      objRS.Open sql, objConn
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
ralmadaCommented:
extra "+" signs there. I've removed them in the below (see line 21, 33 and 46)

sql = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, " & _
             "tblGeneral.Facility, tblGeneral.VADesignation, tblGeneral.FacilityStatus, " & _
             "tblGeneral.LibraryStatus, " & _
             "tblStaff.OnBoardFTE, " & _
             "tblStaff.Series1, tblStaff.Series2, tblStaff.Series3, tblStaff.Series4, tblStaff.Series5, " & _
             "tblStaff.Series6, tblStaff.Series7, tblStaff.Series8, tblStaff.Series9, tblStaff.Series10, " & _
             "tblStaff.PositionStatus1, tblStaff.PositionStatus2, tblStaff.PositionStatus3, tblStaff.PositionStatus4, tblStaff.PositionStatus5, " & _
             "tblStaff.PositionStatus6, tblStaff.PositionStatus7, tblStaff.PositionStatus8, tblStaff.PositionStatus9, tblStaff.PositionStatus10, " & _
             "tblStaff.FTE1, tblStaff.FTE2, tblStaff.FTE3, tblStaff.FTE4, tblStaff.FTE5, " & _
             "tblStaff.FTE6, tblStaff.FTE7, tblStaff.FTE8, tblStaff.FTE9, tblStaff.FTE10, " & _
             "( " & _
            "(select SUM(FTE1) from tblStaff where Series1 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE2) from tblStaff where Series2 = '1411' AND PositionStatus2 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE3) from tblStaff where Series3 = '1411' AND PositionStatus3 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE4) from tblStaff where Series4 = '1411' AND PositionStatus4 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE5) from tblStaff where Series5 = '1411' AND PositionStatus5 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE6) from tblStaff where Series6 = '1411' AND PositionStatus6 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE7) from tblStaff where Series7 = '1411' AND PositionStatus7 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE8) from tblStaff where Series8 = '1411' AND PositionStatus8 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE9) from tblStaff where Series9 = '1411' AND PositionStatus9 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE10) from tblStaff where Series10 = '1411' AND PositionStatus10 = 'Filled' AND ChartID=tblStaff.ChartID)  " & _
            ") AS cnt1411, " & _
             "( " & _
            "(select SUM(FTE1) from tblStaff where Series1 = '1410' AND PositionStatus1 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE2) from tblStaff where Series2 = '1410' AND PositionStatus2 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE3) from tblStaff where Series3 = '1410' AND PositionStatus3 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE4) from tblStaff where Series4 = '1410' AND PositionStatus4 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE5) from tblStaff where Series5 = '1410' AND PositionStatus5 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE6) from tblStaff where Series6 = '1410' AND PositionStatus6 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE7) from tblStaff where Series7 = '1410' AND PositionStatus7 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE8) from tblStaff where Series8 = '1410' AND PositionStatus8 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE9) from tblStaff where Series9 = '1410' AND PositionStatus9 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE10) from tblStaff where Series10 = '1410' AND PositionStatus10 = 'Filled' AND ChartID=tblStaff.ChartID)  " & _
            ") AS cnt1410, " & _

            "( " & _
          "(select SUM(FTE1) from tblStaff where Series1 = 'Other' AND PositionStatus1 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE2) from tblStaff where Series2 = 'Other' AND PositionStatus2 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE3) from tblStaff where Series3 = 'Other' AND PositionStatus3 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE4) from tblStaff where Series4 = 'Other' AND PositionStatus4 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE5) from tblStaff where Series5 = 'Other' AND PositionStatus5 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE6) from tblStaff where Series6 = 'Other' AND PositionStatus6 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE7) from tblStaff where Series7 = 'Other' AND PositionStatus7 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE8) from tblStaff where Series8 = 'Other' AND PositionStatus8 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE9) from tblStaff where Series9 = 'Other' AND PositionStatus9 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE10) from tblStaff where Series10 = 'Other' AND PositionStatus10 = 'Filled' AND ChartID=tblStaff.ChartID)  " & _
            ") AS cntOther " & _


            "FROM tblGeneral LEFT OUTER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID "
             
             If Request.querystring("sort") = "" then
                   sql = sql & "ORDER BY State, City"
             Else
                    sql = sql & "ORDER BY " & Request.querystring("sort")
              End If                  

      Set objRS = Server.CreateObject("ADODB.Recordset")
      objRS.Open sql, objConn 

Open in new window

0
 
JLohmanAuthor Commented:
The table is displaying but the SQL is not counting correctly. All results are displaying as '0'.

 sql = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, " & _
             "tblGeneral.Facility, tblGeneral.VADesignation, tblGeneral.FacilityStatus, " & _
             "tblGeneral.LibraryStatus, tblGeneral.Complexity, " & _
             "tblStaff.OnBoardFTE, " & _
             "tblStaff.Series1, tblStaff.Series2, tblStaff.Series3, tblStaff.Series4, tblStaff.Series5, " & _
             "tblStaff.Series6, tblStaff.Series7, tblStaff.Series8, tblStaff.Series9, tblStaff.Series10, " & _
             "tblStaff.PositionStatus1, tblStaff.PositionStatus2, tblStaff.PositionStatus3, tblStaff.PositionStatus4, tblStaff.PositionStatus5, " & _
             "tblStaff.PositionStatus6, tblStaff.PositionStatus7, tblStaff.PositionStatus8, tblStaff.PositionStatus9, tblStaff.PositionStatus10, " & _
             "tblStaff.FTE1, tblStaff.FTE2, tblStaff.FTE3, tblStaff.FTE4, tblStaff.FTE5, " & _
             "tblStaff.FTE6, tblStaff.FTE7, tblStaff.FTE8, tblStaff.FTE9, tblStaff.FTE10, " & _
             "( " & _
            "(select SUM(FTE1) from tblStaff where Series1 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE2) from tblStaff where Series2 = '1411' AND PositionStatus2 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE3) from tblStaff where Series3 = '1411' AND PositionStatus3 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE4) from tblStaff where Series4 = '1411' AND PositionStatus4 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE5) from tblStaff where Series5 = '1411' AND PositionStatus5 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE6) from tblStaff where Series6 = '1411' AND PositionStatus6 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE7) from tblStaff where Series7 = '1411' AND PositionStatus7 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE8) from tblStaff where Series8 = '1411' AND PositionStatus8 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE9) from tblStaff where Series9 = '1411' AND PositionStatus9 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE10) from tblStaff where Series10 = '1411' AND PositionStatus10 = 'Filled' AND ChartID=tblStaff.ChartID) " & _
            ") AS cnt1411, " & _
             "( " & _
          "(select SUM(FTE1) from tblStaff where Series1 = '1410' AND PositionStatus1 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE2) from tblStaff where Series2 = '1410' AND PositionStatus2 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE3) from tblStaff where Series3 = '1410' AND PositionStatus3 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE4) from tblStaff where Series4 = '1410' AND PositionStatus4 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE5) from tblStaff where Series5 = '1410' AND PositionStatus5 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE6) from tblStaff where Series6 = '1410' AND PositionStatus6 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE7) from tblStaff where Series7 = '1410' AND PositionStatus7 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE8) from tblStaff where Series8 = '1410' AND PositionStatus8 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE9) from tblStaff where Series9 = '1410' AND PositionStatus9 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE10) from tblStaff where Series10 = '1410' AND PositionStatus10 = 'Filled' AND ChartID=tblStaff.ChartID) " & _
            ") AS cnt1410, " & _

            "( " & _
          "(select SUM(FTE1) from tblStaff where Series1 = 'Other' AND PositionStatus1 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE2) from tblStaff where Series2 = 'Other' AND PositionStatus2 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE3) from tblStaff where Series3 = 'Other' AND PositionStatus3 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE4) from tblStaff where Series4 = 'Other' AND PositionStatus4 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE5) from tblStaff where Series5 = 'Other' AND PositionStatus5 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE6) from tblStaff where Series6 = 'Other' AND PositionStatus6 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE7) from tblStaff where Series7 = 'Other' AND PositionStatus7 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE8) from tblStaff where Series8 = 'Other' AND PositionStatus8 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE9) from tblStaff where Series9 = 'Other' AND PositionStatus9 = 'Filled' AND ChartID=tblStaff.ChartID) + " & _
            "(select SUM(FTE10) from tblStaff where Series10 = 'Other' AND PositionStatus10 = 'Filled' AND ChartID=tblStaff.ChartID) " & _
            ") AS cntOther " & _


            "FROM tblGeneral LEFT OUTER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID "
             
             If Request.querystring("sort") = "" then
                   sql = sql & "ORDER BY State, City"
             Else
                    sql = sql & "ORDER BY " & Request.querystring("sort")
              End If                  

      Set objRS = Server.CreateObject("ADODB.Recordset")
      objRS.Open sql, objConn
0
 
ralmadaCommented:
First of all, try to use aliases there.

I think you have some issues with your subqueries there. Check the below code

sql = "SELECT a.ChartID, a.City, a.State, " & _
             "a.Facility, a.VADesignation, a.FacilityStatus, " & _
             "a.LibraryStatus, " & _
             "b.OnBoardFTE, " & _
             "b.Series1, b.Series2, b.Series3, b.Series4, b.Series5, " & _
             "b.Series6, b.Series7, b.Series8, b.Series9, b.Series10, " & _
             "b.PositionStatus1, b.PositionStatus2, b.PositionStatus3, b.PositionStatus4, b.PositionStatus5, " & _
             "b.PositionStatus6, b.PositionStatus7, b.PositionStatus8, b.PositionStatus9, b.PositionStatus10, " & _
             "b.FTE1, b.FTE2, b.FTE3, b.FTE4, b.FTE5, " & _
             "b.FTE6, b.FTE7, b.FTE8, b.FTE9, b.FTE10, " & _
             "( " & _
            "(select SUM(NZ(FTE1, 0)) from tblStaff where Series1 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE2, 0)) from tblStaff where Series2 = '1411' AND PositionStatus2 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE3, 0)) from tblStaff where Series3 = '1411' AND PositionStatus3 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE4, 0)) from tblStaff where Series4 = '1411' AND PositionStatus4 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE5, 0)) from tblStaff where Series5 = '1411' AND PositionStatus5 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE6, 0)) from tblStaff where Series6 = '1411' AND PositionStatus6 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE7, 0)) from tblStaff where Series7 = '1411' AND PositionStatus7 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE8, 0)) from tblStaff where Series8 = '1411' AND PositionStatus8 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE9, 0)) from tblStaff where Series9 = '1411' AND PositionStatus9 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE10, 0)) from tblStaff where Series10 = '1411' AND PositionStatus10 = 'Filled' AND ChartID=a.ChartID)  " & _
            ") AS cnt1411, " & _
             "( " & _
            "(select SUM(NZ(FTE1, 0)) from tblStaff where Series1 = '1410' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE2, 0)) from tblStaff where Series2 = '1410' AND PositionStatus2 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE3, 0)) from tblStaff where Series3 = '1410' AND PositionStatus3 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE4, 0)) from tblStaff where Series4 = '1410' AND PositionStatus4 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE5, 0)) from tblStaff where Series5 = '1410' AND PositionStatus5 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE6, 0)) from tblStaff where Series6 = '1410' AND PositionStatus6 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE7, 0)) from tblStaff where Series7 = '1410' AND PositionStatus7 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE8, 0)) from tblStaff where Series8 = '1410' AND PositionStatus8 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE9, 0)) from tblStaff where Series9 = '1410' AND PositionStatus9 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE10, 0)) from tblStaff where Series10 = '1410' AND PositionStatus10 = 'Filled' AND ChartID=a.ChartID)  " & _
            ") AS cnt1410, " & _

            "( " & _
          "(select SUM(NZ(FTE1, 0)) from tblStaff where Series1 = 'Other' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE2, 0)) from tblStaff where Series2 = 'Other' AND PositionStatus2 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE3, 0)) from tblStaff where Series3 = 'Other' AND PositionStatus3 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE4, 0)) from tblStaff where Series4 = 'Other' AND PositionStatus4 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE5, 0)) from tblStaff where Series5 = 'Other' AND PositionStatus5 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE6, 0)) from tblStaff where Series6 = 'Other' AND PositionStatus6 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE7, 0)) from tblStaff where Series7 = 'Other' AND PositionStatus7 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE8, 0)) from tblStaff where Series8 = 'Other' AND PositionStatus8 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE9, 0)) from tblStaff where Series9 = 'Other' AND PositionStatus9 = 'Filled' AND ChartID=a.ChartID) + " & _
            "(select SUM(NZ(FTE10, 0)) from tblStaff where Series10 = 'Other' AND PositionStatus10 = 'Filled' AND ChartID=a.ChartID)  " & _
            ") AS cntOther " & _


            "FROM tblGeneral a LEFT OUTER JOIN tblStaff b ON a.ChartID= b.ChartID "
             
             If Request.querystring("sort") = "" then
                   sql = sql & "ORDER BY State, City"
             Else
                    sql = sql & "ORDER BY " & Request.querystring("sort")
              End If                  

      Set objRS = Server.CreateObject("ADODB.Recordset")
      objRS.Open sql, objConn 

Open in new window

0
 
JLohmanAuthor Commented:
The code is now generating the following error:

 Undefined function 'NZ' in expression

Is the '0' in this statement going to eliminate numbers to the right of the decimal? I f so, I suppose I can change the '0' to a '2'
0
 
ralmadaCommented:
NZ is an access function to turn nulls into something (zeros in this case).

Try without it or use

...sum(iif(FTE1 is null, 0, FTE1))...
0
 
JLohmanAuthor Commented:
I have tried several different formats:

"(select SUM(FTE2) from tblStaff where Series2 = 'Other' AND PositionStatus2 = 'Filled' AND ChartID=a.ChartID) + " & _

I also tried:
"(select SUM(iif(FTE1 is null, 0, FTE1))  from tblStaff where Series4 = '1410' AND PositionStatus4 = 'Filled' AND ChartID=a.ChartID) + " & _

I don't know what to try next!
0
 
ralmadaCommented:
so what's the issue with them? Can you post some sample data and the expected result?
0
 
JLohmanAuthor Commented:
I am attaching a copy of the database.

I am also attaching a copy of what the report should look like.

Column 1410 is cnt1410
Column 1411 is cnt1411
Column Other is cntOther

cnt1410 + cnt1411 + cntOther should equal the Column OnBoard FTE which is field objRS("OnBoardFTE")

Appreciate you working through this...
152Staff.mdb
152.GIF
0
 
ralmadaCommented:
This works in Access, now if the NZ function doesn't do the trick for you
 
            (  
            NZ((select SUM(FTE1) from tblStaff where Series1 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID), 0) +  
            NZ((select SUM(FTE2) from tblStaff where Series2 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID), 0) +  
            NZ((select SUM(FTE3) from tblStaff where Series3 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID), 0) +  
            NZ((select SUM(FTE4) from tblStaff where Series4 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID), 0) +  
            NZ((select SUM(FTE5) from tblStaff where Series5 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID), 0) +  
            NZ((select SUM(FTE6) from tblStaff where Series6 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID), 0) +  
            NZ((select SUM(FTE7) from tblStaff where Series7 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID), 0) +  
            NZ((select SUM(FTE8) from tblStaff where Series8 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID), 0) +  
            NZ((select SUM(FTE9) from tblStaff where Series9 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID), 0) +  
            NZ((select SUM(FTE10) from tblStaff where Series10 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID), 0) 
            ) as cnt1411,  

Open in new window


If not, using iif

             (  
........
            iif(isnull((select SUM(FTE1) from tblStaff where Series1 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE1) from tblStaff where Series1 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID)) +  
            iif(isnull((select SUM(FTE2) from tblStaff where Series2 = '1411' AND PositionStatus2 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE2) from tblStaff where Series2 = '1411' AND PositionStatus2 = 'Filled' AND ChartID=a.ChartID)) +  
            iif(isnull((select SUM(FTE3) from tblStaff where Series3 = '1411' AND PositionStatus3 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE3) from tblStaff where Series3 = '1411' AND PositionStatus3 = 'Filled' AND ChartID=a.ChartID)) +  
            iif(isnull((select SUM(FTE4) from tblStaff where Series4 = '1411' AND PositionStatus4 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE4) from tblStaff where Series4 = '1411' AND PositionStatus4 = 'Filled' AND ChartID=a.ChartID)) +  
            iif(isnull((select SUM(FTE5) from tblStaff where Series5 = '1411' AND PositionStatus5 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE5) from tblStaff where Series5 = '1411' AND PositionStatus5 = 'Filled' AND ChartID=a.ChartID)) +  
            iif(isnull((select SUM(FTE6) from tblStaff where Series6 = '1411' AND PositionStatus6 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE6) from tblStaff where Series6 = '1411' AND PositionStatus6 = 'Filled' AND ChartID=a.ChartID)) +  
            iif(isnull((select SUM(FTE7) from tblStaff where Series7 = '1411' AND PositionStatus7 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE7) from tblStaff where Series7 = '1411' AND PositionStatus7 = 'Filled' AND ChartID=a.ChartID)) +  
            iif(isnull((select SUM(FTE8) from tblStaff where Series8 = '1411' AND PositionStatus8 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE8) from tblStaff where Series8 = '1411' AND PositionStatus8 = 'Filled' AND ChartID=a.ChartID)) +  
            iif(isnull((select SUM(FTE9) from tblStaff where Series9 = '1411' AND PositionStatus9 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE9) from tblStaff where Series9 = '1411' AND PositionStatus9 = 'Filled' AND ChartID=a.ChartID)) +  
            iif(isnull((select SUM(FTE10) from tblStaff where Series10= '1411' AND PositionStatus10 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE10) from tblStaff where Series10= '1411' AND PositionStatus10= 'Filled' AND ChartID=a.ChartID))   

            ) as cnt1411
....

Open in new window

0
 
JLohmanAuthor Commented:
Very frustrating....

Code is:
                  "NZ((select SUM(FTE1) from tblStaff where Series1 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID), 0)) + " & _
                  "NZ((select SUM(FTE2) from tblStaff where Series2 = '1411' AND PositionStatus2 = 'Filled' AND ChartID=a.ChartID), 0)) + " & _
                  "NZ((select SUM(FTE3) from tblStaff where Series3 = '1411' AND PositionStatus3 = 'Filled' AND ChartID=a.ChartID), 0)) + " & _
                  "NZ((select SUM(FTE4) from tblStaff where Series4 = '1411' AND PositionStatus4 = 'Filled' AND ChartID=a.ChartID), 0)) + " & _
                  "NZ((select SUM(FTE5) from tblStaff where Series5 = '1411' AND PositionStatus5 = 'Filled' AND ChartID=a.ChartID), 0)) + " & _
                  "NZ((select SUM(FTE6) from tblStaff where Series6 = '1411' AND PositionStatus6 = 'Filled' AND ChartID=a.ChartID), 0)) + " & _
                  "NZ((select SUM(FTE7) from tblStaff where Series7 = '1411' AND PositionStatus7 = 'Filled' AND ChartID=a.ChartID), 0)) + " & _
                  "NZ((select SUM(FTE8) from tblStaff where Series8 = '1411' AND PositionStatus8 = 'Filled' AND ChartID=a.ChartID), 0)) + " & _
                  "NZ((select SUM(FTE9) from tblStaff where Series9 = '1411' AND PositionStatus9 = 'Filled' AND ChartID=a.ChartID), 0)) + " & _
                  "NZ((select SUM(FTE10) from tblStaff where Series10 = '1411' AND PositionStatus10 = 'Filled' AND ChartID=a.ChartID), 0)) " & _
            ") as cnt1411, " & _            

Which generates the following error:

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

[Microsoft][ODBC Microsoft Access Driver] Extra ) in query expression '( NZ((select SUM(FTE1) from tblStaff where Series1 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID), 0)) + NZ((select SUM(FTE2) from tblStaff where Series2 = '1411' AND PositionStatus2 = 'Filled' AND ChartID=a.ChartID), 0)) + NZ((select SUM(FTE'

When I delete the 2nd ) after the '0'...
"NZ((select SUM(FTE1) from tblStaff where Series1 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID), 0) + " & _
I get the following error:

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

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'NZ' in expression.


The iif statement
                     "iif(isnull((select SUM(FTE1) from tblStaff where Series1 = '1410' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE1) from tblStaff where Series1 = '1410' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID)) + " & _
                     "iif(isnull((select SUM(FTE2) from tblStaff where Series2 = '1410' AND PositionStatus2 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE2) from tblStaff where Series2 = '1410' AND PositionStatus2 = 'Filled' AND ChartID=a.ChartID)) + " & _
            "iif(isnull((select SUM(FTE3) from tblStaff where Series3 = '1410' AND PositionStatus3 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE3) from tblStaff where Series3 = '1410' AND PositionStatus3 = 'Filled' AND ChartID=a.ChartID)) + " & _

Is still returning all 0s.
0
 
JLohmanAuthor Commented:
Does anyone see anything wrong with my iif statement? Results are displaying all ZEROs.

 "( " & _
"iif(isnull((select SUM(FTE1) from tblStaff where Series1 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE1) from tblStaff where Series1 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=a.ChartID)) + " & _
            "iif(isnull((select SUM(FTE2) from tblStaff where Series2 = '1411' AND PositionStatus2 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE2) from tblStaff where Series2 = '1411' AND PositionStatus2 = 'Filled' AND ChartID=a.ChartID)) + " & _
            "iif(isnull((select SUM(FTE3) from tblStaff where Series3 = '1411' AND PositionStatus3 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE3) from tblStaff where Series3 = '1411' AND PositionStatus3 = 'Filled' AND ChartID=a.ChartID)) + " & _
            "iif(isnull((select SUM(FTE4) from tblStaff where Series4 = '1411' AND PositionStatus4 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE4) from tblStaff where Series4 = '1411' AND PositionStatus4 = 'Filled' AND ChartID=a.ChartID)) + " & _
            "iif(isnull((select SUM(FTE5) from tblStaff where Series5 = '1411' AND PositionStatus5 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE5) from tblStaff where Series5 = '1411' AND PositionStatus5 = 'Filled' AND ChartID=a.ChartID)) + " & _
            "iif(isnull((select SUM(FTE6) from tblStaff where Series6 = '1411' AND PositionStatus6 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE6) from tblStaff where Series6 = '1411' AND PositionStatus6 = 'Filled' AND ChartID=a.ChartID)) + " & _
            "iif(isnull((select SUM(FTE7) from tblStaff where Series7 = '1411' AND PositionStatus7 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE7) from tblStaff where Series7 = '1411' AND PositionStatus7 = 'Filled' AND ChartID=a.ChartID)) + " & _
            "iif(isnull((select SUM(FTE8) from tblStaff where Series8 = '1411' AND PositionStatus8 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE8) from tblStaff where Series8 = '1411' AND PositionStatus8 = 'Filled' AND ChartID=a.ChartID)) + " & _
            "iif(isnull((select SUM(FTE9) from tblStaff where Series9 = '1411' AND PositionStatus9 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE9) from tblStaff where Series9 = '1411' AND PositionStatus9 = 'Filled' AND ChartID=a.ChartID)) + " & _
            "iif(isnull((select SUM(FTE10) from tblStaff where Series10= '1411' AND PositionStatus10 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE10) from tblStaff where Series10= '1411' AND PositionStatus10= 'Filled' AND ChartID=a.ChartID)) " & _  
            ") as cnt1411, " & _            
            "( " & _
0
 
joaoalmeidaCommented:
Instead of

iif(isnull((select SUM(FTE2) from tblStaff where Series2 = '1411' AND PositionStatus2 = 'Filled' AND ChartID=a.ChartID)), 0, (select SUM(FTE2) from tblStaff where Series2 = '1411' AND

try

(select SUM(iif(isnull(FTE2),0,FTE2)) from tblStaff where Series2 = '1411' AND PositionStatus2 = 'Filled' AND ChartID=a.ChartID), ..............

It will also be faster.
To make a test try only with one of the series to avoid replacing the entire sql query.
0
 
JLohmanAuthor Commented:
It is still returning all values of '0'
0
 
JLohmanAuthor Commented:
I replaced the original query with the recommendation.

Code is displaying a report, but the results for the Series1411 are all displaying as zeros.
0
 
hnasrCommented:
Sorry, I couldn't find may way through the problem.

if you can, upload a reproduced copy with just few records and few fields. Once a solution is achieved one can expand to cover many fields.
0
 
aikimarkCommented:
This query worked in your database.  During my analysis I got an error message indicating that too many line continuations were used.  You should change your code to build up the sql variable string in multiple concatenation steps.
Select tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.Facility, tblGeneral.VADesignation, tblGeneral.FacilityStatus, tblGeneral.LibraryStatus, tblStaff.OnBoardFTE, tblStaff.Series1, tblStaff.Series2, tblStaff.Series3, tblStaff.Series4, tblStaff.Series5, tblStaff.Series6, tblStaff.Series7, tblStaff.Series8, tblStaff.Series9, tblStaff.Series10, tblStaff.PositionStatus1, tblStaff.PositionStatus2, tblStaff.PositionStatus3, tblStaff.PositionStatus4, tblStaff.PositionStatus5, tblStaff.PositionStatus6, tblStaff.PositionStatus7, tblStaff.PositionStatus8, tblStaff.PositionStatus9, tblStaff.PositionStatus10, tblStaff.FTE1, tblStaff.FTE2, tblStaff.FTE3, tblStaff.FTE4, tblStaff.FTE5, tblStaff.FTE6, tblStaff.FTE7, tblStaff.FTE8, tblStaff.FTE9, tblStaff.FTE10, 
(
 (select SUM(nz(FTE1)+nz(FTE2)+nz(FTE3)+nz(FTE4)+nz(FTE5)+nz(FTE6)+nz(FTE7)+nz(FTE8)+nz(FTE9)+nz(FTE10)) from tblStaff where Series1 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=tblStaff.ChartID) 
 ) AS cnt1411,
(
  (select SUM(nz(FTE1)+nz(FTE2)+nz(FTE3)+nz(FTE4)+nz(FTE5)+nz(FTE6)+nz(FTE7)+nz(FTE8)+nz(FTE9)+nz(FTE10)) from tblStaff where Series1 = '1410' AND PositionStatus1 = 'Filled' AND ChartID=tblStaff.ChartID) 
) AS cnt1410, 
( 
  (select SUM(nz(FTE1)+nz(FTE2)+nz(FTE3)+nz(FTE4)+nz(FTE5)+nz(FTE6)+nz(FTE7)+nz(FTE8)+nz(FTE9)+nz(FTE10)) from tblStaff where Series1 = 'Other' AND PositionStatus1 = 'Filled' AND ChartID=tblStaff.ChartID) 
) AS cntOther 
FROM tblGeneral LEFT OUTER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID 

Open in new window

0
 
aikimarkCommented:
Note: I edited my prior comment as well as this one.

I forgot to include the NZ() function.  While I was at it, I simplified the SQL and the
Example code.
sql = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.Facility, tblGeneral.VADesignation, tblGeneral.FacilityStatus, tblGeneral.LibraryStatus, tblStaff.OnBoardFTE, tblStaff.Series1, tblStaff.Series2, tblStaff.Series3, tblStaff.Series4, tblStaff.Series5, tblStaff.Series6, tblStaff.Series7, tblStaff.Series8, tblStaff.Series9, tblStaff.Series10, tblStaff.PositionStatus1, tblStaff.PositionStatus2, tblStaff.PositionStatus3, tblStaff.PositionStatus4, tblStaff.PositionStatus5, tblStaff.PositionStatus6, tblStaff.PositionStatus7, tblStaff.PositionStatus8, tblStaff.PositionStatus9, tblStaff.PositionStatus10, tblStaff.FTE1, tblStaff.FTE2, tblStaff.FTE3, tblStaff.FTE4, tblStaff.FTE5, tblStaff.FTE6, tblStaff.FTE7, tblStaff.FTE8, tblStaff.FTE9, tblStaff.FTE10, "
sql = sql & "( " & _
   " (select SUM(nz(FTE1)+nz(FTE2)+nz(FTE3)+nz(FTE4)+nz(FTE5)+nz(FTE6)+nz(FTE7)+nz(FTE8)+nz(FTE9)+nz(FTE10)) from tblStaff where Series1 = '1411' AND PositionStatus1 = 'Filled' AND ChartID=tblStaff.ChartID)  " & _
   "  ) AS cnt1411, " & _
   " ( " & _
   "   (select SUM(nz(FTE1)+nz(FTE2)+nz(FTE3)+nz(FTE4)+nz(FTE5)+nz(FTE6)+nz(FTE7)+nz(FTE8)+nz(FTE9)+nz(FTE10)) from tblStaff where Series1 = '1410' AND PositionStatus1 = 'Filled' AND ChartID=tblStaff.ChartID)  " & _
   " ) AS cnt1410,  " & _
   " (  " & _
   "   (select SUM(nz(FTE1)+nz(FTE2)+nz(FTE3)+nz(FTE4)+nz(FTE5)+nz(FTE6)+nz(FTE7)+nz(FTE8)+nz(FTE9)+nz(FTE10)) from tblStaff where Series1 = 'Other' AND PositionStatus1 = 'Filled' AND ChartID=tblStaff.ChartID)  " & _
   " ) AS cntOther  " & _
   " FROM tblGeneral LEFT OUTER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID  " 

Open in new window

0
 
JLohmanAuthor Commented:
The above example is not calculating correctly.
Each record has FTE1 thru FTE10, with associated Series1 thru Series10 and PositionStatusI thru PositionStatus10. I need a SUM of the following.

FTE1 (where Series1 = 1410 and PositionStatus1 = Filled) +
FTE2 (where Series2 = 1410 and PositionStatus2 = Filled) +
FTE3 (where Series3 = 1410 and PositionStatus3 = Filled) +
FTE4 (where Series4 = 1410 and PositionStatus4 = Filled) +
FTE5 (where Series5 = 1410 and PositionStatus5 = Filled) +
FTE6 (where Series6 = 1410 and PositionStatus6 = Filled) +
FTE7 (where Series7 = 1410 and PositionStatus7 = Filled) +
FTE8 (where Series8 = 1410 and PositionStatus8 = Filled) +
FTE9 (where Series9 = 1410 and PositionStatus9 = Filled) +
FTE10 (where Series10 = 1410 and PositionStatus10 = Filled)

An example using NZ was used before. Then I was getting a "Undefined function 'NZ' in expression" error.
0
 
aikimarkCommented:
my bad.  I copy/pasted the lines and change & missed the PositionStatus# differences. :-(
0
 
aikimarkCommented:
Please confirm that the back-end database is MS Access
0
 
aikimarkCommented:
Please bear with me on this lengthy answer.

I stepped back and rendered this query in a somewhat verbose manner (think Excel)
SELECT tblGeneral.ChartID, tblGeneral.Facility, tblGeneral.City, tblGeneral.State, tblGeneral.FacilityStatus, tblGeneral.VADesignation, tblGeneral.VISNID, tblGeneral.Complexity, tblGeneral.LibraryStatus, tblStaff.OnBoardFTE, tblStaff.Title1, tblStaff.FTE1, tblStaff.Series1, tblStaff.PositionStatus1, tblStaff.Title2, tblStaff.FTE2, tblStaff.Series2, tblStaff.PositionStatus2, tblStaff.Title3, tblStaff.FTE3, tblStaff.Series3, tblStaff.PositionStatus3, tblStaff.Title4, tblStaff.FTE4, tblStaff.Series4, tblStaff.PositionStatus4, tblStaff.Title5, tblStaff.FTE5, tblStaff.Series5, tblStaff.PositionStatus5, tblStaff.Title6, tblStaff.FTE6, tblStaff.Series6, tblStaff.PositionStatus6, tblStaff.Title7, tblStaff.FTE7, tblStaff.Series7, tblStaff.PositionStatus7, tblStaff.Title8, tblStaff.FTE8, tblStaff.Series8, tblStaff.PositionStatus8, tblStaff.Title9, tblStaff.FTE9, tblStaff.Series9, tblStaff.PositionStatus9, tblStaff.Title10, tblStaff.FTE10, tblStaff.Series10, tblStaff.PositionStatus10, 
(Select Sum(TS.FTE1) From tblStaff As TS where TS.Series1 = '1411' AND TS.PositionStatus1 = 'Filled' AND TS.ChartID=ChartID) AS FTE1_1411_Filled, IsNull(FTE1_1411_Filled) AS FTE1_1411_Filled_Null, 
(Select Sum(TS.FTE2) From tblStaff As TS where TS.Series2 = '1411' AND TS.PositionStatus2 = 'Filled' AND TS.ChartID=ChartID) AS FTE2_1411_Filled, IsNull(FTE2_1411_Filled) AS FTE2_1411_Filled_Null, 
(Select Sum(TS.FTE3) From tblStaff As TS where TS.Series3 = '1411' AND TS.PositionStatus3 = 'Filled' AND TS.ChartID=ChartID) AS FTE3_1411_Filled, IsNull(FTE3_1411_Filled) AS FTE3_1411_Filled_Null, 
(Select Sum(TS.FTE4) From tblStaff As TS where TS.Series4 = '1411' AND TS.PositionStatus4 = 'Filled' AND TS.ChartID=ChartID) AS FTE4_1411_Filled, IsNull(FTE4_1411_Filled) AS FTE4_1411_Filled_Null,
(Select Sum(TS.FTE5) From tblStaff As TS where TS.Series5 = '1411' AND TS.PositionStatus5 = 'Filled' AND TS.ChartID=ChartID) AS FTE5_1411_Filled, IsNull(FTE5_1411_Filled) AS FTE5_1411_Filled_Null,
(Select Sum(TS.FTE6) From tblStaff As TS where TS.Series6 = '1411' AND TS.PositionStatus6 = 'Filled' AND TS.ChartID=ChartID) AS FTE6_1411_Filled, IsNull(FTE6_1411_Filled) AS FTE6_1411_Filled_Null,
(Select Sum(TS.FTE7) From tblStaff As TS where TS.Series7 = '1411' AND TS.PositionStatus7 = 'Filled' AND TS.ChartID=ChartID) AS FTE7_1411_Filled, IsNull(FTE7_1411_Filled) AS FTE7_1411_Filled_Null,
(Select Sum(TS.FTE8) From tblStaff As TS where TS.Series8 = '1411' AND TS.PositionStatus8 = 'Filled' AND TS.ChartID=ChartID) AS FTE8_1411_Filled, IsNull(FTE8_1411_Filled) AS FTE8_1411_Filled_Null,
(Select Sum(TS.FTE9) From tblStaff As TS where TS.Series9 = '1411' AND TS.PositionStatus9 = 'Filled' AND TS.ChartID=ChartID) AS FTE9_1411_Filled, IsNull(FTE9_1411_Filled) AS FTE9_1411_Filled_Null,
(Select Sum(TS.FTE10) From tblStaff As TS where TS.Series10 = '1411' AND TS.PositionStatus10 = 'Filled' AND TS.ChartID=ChartID) AS FTE10_1411_Filled, IsNull(FTE10_1411_Filled) AS FTE10_1411_Filled_Null,


(Select Sum(TS.FTE1) From tblStaff As TS where TS.Series1 = '1410' AND TS.PositionStatus1 = 'Filled' AND TS.ChartID=ChartID) AS FTE1_1410_Filled, IsNull(FTE1_1410_Filled) AS FTE1_1410_Filled_Null, 
(Select Sum(TS.FTE2) From tblStaff As TS where TS.Series2 = '1410' AND TS.PositionStatus2 = 'Filled' AND TS.ChartID=ChartID) AS FTE2_1410_Filled, IsNull(FTE2_1410_Filled) AS FTE2_1410_Filled_Null, 
(Select Sum(TS.FTE3) From tblStaff As TS where TS.Series3 = '1410' AND TS.PositionStatus3 = 'Filled' AND TS.ChartID=ChartID) AS FTE3_1410_Filled, IsNull(FTE3_1410_Filled) AS FTE3_1410_Filled_Null, 
(Select Sum(TS.FTE4) From tblStaff As TS where TS.Series4 = '1410' AND TS.PositionStatus4 = 'Filled' AND TS.ChartID=ChartID) AS FTE4_1410_Filled, IsNull(FTE4_1410_Filled) AS FTE4_1410_Filled_Null,
(Select Sum(TS.FTE5) From tblStaff As TS where TS.Series5 = '1410' AND TS.PositionStatus5 = 'Filled' AND TS.ChartID=ChartID) AS FTE5_1410_Filled, IsNull(FTE5_1410_Filled) AS FTE5_1410_Filled_Null,
(Select Sum(TS.FTE6) From tblStaff As TS where TS.Series6 = '1410' AND TS.PositionStatus6 = 'Filled' AND TS.ChartID=ChartID) AS FTE6_1410_Filled, IsNull(FTE6_1410_Filled) AS FTE6_1410_Filled_Null,
(Select Sum(TS.FTE7) From tblStaff As TS where TS.Series7 = '1410' AND TS.PositionStatus7 = 'Filled' AND TS.ChartID=ChartID) AS FTE7_1410_Filled, IsNull(FTE7_1410_Filled) AS FTE7_1410_Filled_Null,
(Select Sum(TS.FTE8) From tblStaff As TS where TS.Series8 = '1410' AND TS.PositionStatus8 = 'Filled' AND TS.ChartID=ChartID) AS FTE8_1410_Filled, IsNull(FTE8_1410_Filled) AS FTE8_1410_Filled_Null,
(Select Sum(TS.FTE9) From tblStaff As TS where TS.Series9 = '1410' AND TS.PositionStatus9 = 'Filled' AND TS.ChartID=ChartID) AS FTE9_1410_Filled, IsNull(FTE9_1410_Filled) AS FTE9_1410_Filled_Null,
(Select Sum(TS.FTE10) From tblStaff As TS where TS.Series10 = '1410' AND TS.PositionStatus10 = 'Filled' AND TS.ChartID=ChartID) AS FTE10_1410_Filled, IsNull(FTE10_1410_Filled) AS FTE10_1410_Filled_Null,

(Select Sum(TS.FTE1) From tblStaff As TS where TS.Series1 = 'Other' AND TS.PositionStatus1 = 'Filled' AND TS.ChartID=ChartID) AS FTE1_Other_Filled, IsNull(FTE1_Other_Filled) AS FTE1_Other_Filled_Null, 
(Select Sum(TS.FTE2) From tblStaff As TS where TS.Series2 = 'Other' AND TS.PositionStatus2 = 'Filled' AND TS.ChartID=ChartID) AS FTE2_Other_Filled, IsNull(FTE2_Other_Filled) AS FTE2_Other_Filled_Null, 
(Select Sum(TS.FTE3) From tblStaff As TS where TS.Series3 = 'Other' AND TS.PositionStatus3 = 'Filled' AND TS.ChartID=ChartID) AS FTE3_Other_Filled, IsNull(FTE3_Other_Filled) AS FTE3_Other_Filled_Null, 
(Select Sum(TS.FTE4) From tblStaff As TS where TS.Series4 = 'Other' AND TS.PositionStatus4 = 'Filled' AND TS.ChartID=ChartID) AS FTE4_Other_Filled, IsNull(FTE4_Other_Filled) AS FTE4_Other_Filled_Null,
(Select Sum(TS.FTE5) From tblStaff As TS where TS.Series5 = 'Other' AND TS.PositionStatus5 = 'Filled' AND TS.ChartID=ChartID) AS FTE5_Other_Filled, IsNull(FTE5_Other_Filled) AS FTE5_Other_Filled_Null,
(Select Sum(TS.FTE6) From tblStaff As TS where TS.Series6 = 'Other' AND TS.PositionStatus6 = 'Filled' AND TS.ChartID=ChartID) AS FTE6_Other_Filled, IsNull(FTE6_Other_Filled) AS FTE6_Other_Filled_Null,
(Select Sum(TS.FTE7) From tblStaff As TS where TS.Series7 = 'Other' AND TS.PositionStatus7 = 'Filled' AND TS.ChartID=ChartID) AS FTE7_Other_Filled, IsNull(FTE7_Other_Filled) AS FTE7_Other_Filled_Null,
(Select Sum(TS.FTE8) From tblStaff As TS where TS.Series8 = 'Other' AND TS.PositionStatus8 = 'Filled' AND TS.ChartID=ChartID) AS FTE8_Other_Filled, IsNull(FTE8_Other_Filled) AS FTE8_Other_Filled_Null,
(Select Sum(TS.FTE9) From tblStaff As TS where TS.Series9 = 'Other' AND TS.PositionStatus9 = 'Filled' AND TS.ChartID=ChartID) AS FTE9_Other_Filled, IsNull(FTE9_Other_Filled) AS FTE9_Other_Filled_Null,
(Select Sum(TS.FTE10) From tblStaff As TS where TS.Series10 = 'Other' AND TS.PositionStatus10 = 'Filled' AND TS.ChartID=ChartID) AS FTE10_Other_Filled, IsNull(FTE10_Other_Filled) AS FTE10_Other_Filled_Null,

(IIF(FTE1_1411_Filled_Null,0, FTE1_1411_Filled) + IIF(FTE2_1411_Filled_Null,0, FTE2_1411_Filled) + IIF(FTE3_1411_Filled_Null,0, FTE3_1411_Filled)
 + IIF(FTE4_1411_Filled_Null,0, FTE4_1411_Filled) + IIF(FTE5_1411_Filled_Null,0, FTE5_1411_Filled) + IIF(FTE6_1411_Filled_Null,0, FTE6_1411_Filled)
 + IIF(FTE7_1411_Filled_Null,0, FTE7_1411_Filled) + IIF(FTE8_1411_Filled_Null,0, FTE8_1411_Filled) + IIF(FTE9_1411_Filled_Null,0, FTE9_1411_Filled)
 + IIF(FTE10_1411_Filled_Null,0, FTE10_1411_Filled)) As cnt1411,

(IIF(FTE1_1410_Filled_Null,0, FTE1_1410_Filled) + IIF(FTE2_1410_Filled_Null,0, FTE2_1410_Filled) + IIF(FTE3_1410_Filled_Null,0, FTE3_1410_Filled)
 + IIF(FTE4_1410_Filled_Null,0, FTE4_1410_Filled) + IIF(FTE5_1410_Filled_Null,0, FTE5_1410_Filled) + IIF(FTE6_1410_Filled_Null,0, FTE6_1410_Filled)
 + IIF(FTE7_1410_Filled_Null,0, FTE7_1410_Filled) + IIF(FTE8_1410_Filled_Null,0, FTE8_1410_Filled) + IIF(FTE9_1410_Filled_Null,0, FTE9_1410_Filled)
 + IIF(FTE10_1410_Filled_Null,0, FTE10_1410_Filled)) As cnt1410,

(IIF(FTE1_Other_Filled_Null,0, FTE1_Other_Filled) + IIF(FTE2_Other_Filled_Null,0, FTE2_Other_Filled) + IIF(FTE3_Other_Filled_Null,0, FTE3_Other_Filled)
 + IIF(FTE4_Other_Filled_Null,0, FTE4_Other_Filled) + IIF(FTE5_Other_Filled_Null,0, FTE5_Other_Filled) + IIF(FTE6_Other_Filled_Null,0, FTE6_Other_Filled)
 + IIF(FTE7_Other_Filled_Null,0, FTE7_Other_Filled) + IIF(FTE8_Other_Filled_Null,0, FTE8_Other_Filled) + IIF(FTE9_Other_Filled_Null,0, FTE9_Other_Filled)
 + IIF(FTE10_Other_Filled_Null,0, FTE10_Other_Filled)) As cntOther

FROM tblGeneral LEFT JOIN tblStaff ON tblGeneral.ChartID = tblStaff.ChartID;

Open in new window


Then, I realized that the two tables are being joined in the main query as well as the correlated sub-queries. It occurred to me that if there aren't duplicate ChartID values, we don't need a Sum in the correlated sub-queries, just a check for null values and data-eligible conditions.  I'll post both the simplified form and a link to an excellent article on functions that perform domain functions on a single row.
http:A_1775-Computing-row-wise-aggregations-in-Access.html

Query if there are no duplicate ChartID values.
SELECT tblGeneral.ChartID, tblGeneral.Facility, tblGeneral.City, tblGeneral.State, tblGeneral.FacilityStatus, tblGeneral.VADesignation, tblGeneral.VISNID, tblGeneral.Complexity, tblGeneral.LibraryStatus, tblStaff.OnBoardFTE, tblStaff.Title1, tblStaff.FTE1, tblStaff.Series1, tblStaff.PositionStatus1, tblStaff.Title2, tblStaff.FTE2, tblStaff.Series2, tblStaff.PositionStatus2, tblStaff.Title3, tblStaff.FTE3, tblStaff.Series3, tblStaff.PositionStatus3, tblStaff.Title4, tblStaff.FTE4, tblStaff.Series4, tblStaff.PositionStatus4, tblStaff.Title5, tblStaff.FTE5, tblStaff.Series5, tblStaff.PositionStatus5, tblStaff.Title6, tblStaff.FTE6, tblStaff.Series6, tblStaff.PositionStatus6, tblStaff.Title7, tblStaff.FTE7, tblStaff.Series7, tblStaff.PositionStatus7, tblStaff.Title8, tblStaff.FTE8, tblStaff.Series8, tblStaff.PositionStatus8, tblStaff.Title9, tblStaff.FTE9, tblStaff.Series9, tblStaff.PositionStatus9, tblStaff.Title10, tblStaff.FTE10, tblStaff.Series10, tblStaff.PositionStatus10, 
( IIF(Not(IsNull(FTE1)) AND Series1 = '1411' AND PositionStatus1 = 'Filled', FTE1, 0) 
+ IIF(Not(IsNull(FTE2)) AND Series2 = '1411' AND PositionStatus2 = 'Filled', FTE2, 0) 
+ IIF(Not(IsNull(FTE3)) AND Series3 = '1411' AND PositionStatus3 = 'Filled', FTE3, 0) 
+ IIF(Not(IsNull(FTE4)) AND Series4 = '1411' AND PositionStatus4 = 'Filled', FTE4, 0) 
+ IIF(Not(IsNull(FTE5)) AND Series5 = '1411' AND PositionStatus5 = 'Filled', FTE5, 0) 
+ IIF(Not(IsNull(FTE6)) AND Series6 = '1411' AND PositionStatus6 = 'Filled', FTE6, 0) 
+ IIF(Not(IsNull(FTE7)) AND Series7 = '1411' AND PositionStatus7 = 'Filled', FTE7, 0) 
+ IIF(Not(IsNull(FTE8)) AND Series8 = '1411' AND PositionStatus8 = 'Filled', FTE8, 0) 
+ IIF(Not(IsNull(FTE9)) AND Series9 = '1411' AND PositionStatus9 = 'Filled', FTE9, 0) 
+ IIF(Not(IsNull(FTE10)) AND Series10 = '1411' AND PositionStatus10 = 'Filled', FTE10, 0) 
) As cnt1411,
( IIF(Not(IsNull(FTE1)) AND Series1 = '1410' AND PositionStatus1 = 'Filled', FTE1, 0) 
+ IIF(Not(IsNull(FTE2)) AND Series2 = '1410' AND PositionStatus2 = 'Filled', FTE2, 0) 
+ IIF(Not(IsNull(FTE3)) AND Series3 = '1410' AND PositionStatus3 = 'Filled', FTE3, 0) 
+ IIF(Not(IsNull(FTE4)) AND Series4 = '1410' AND PositionStatus4 = 'Filled', FTE4, 0) 
+ IIF(Not(IsNull(FTE5)) AND Series5 = '1410' AND PositionStatus5 = 'Filled', FTE5, 0) 
+ IIF(Not(IsNull(FTE6)) AND Series6 = '1410' AND PositionStatus6 = 'Filled', FTE6, 0) 
+ IIF(Not(IsNull(FTE7)) AND Series7 = '1410' AND PositionStatus7 = 'Filled', FTE7, 0) 
+ IIF(Not(IsNull(FTE8)) AND Series8 = '1410' AND PositionStatus8 = 'Filled', FTE8, 0) 
+ IIF(Not(IsNull(FTE9)) AND Series9 = '1410' AND PositionStatus9 = 'Filled', FTE9, 0) 
+ IIF(Not(IsNull(FTE10)) AND Series10 = '1410' AND PositionStatus10 = 'Filled', FTE10, 0) 
) As cnt1410,
( IIF(Not(IsNull(FTE1)) AND Series1 = 'Other' AND PositionStatus1 = 'Filled', FTE1, 0) 
+ IIF(Not(IsNull(FTE2)) AND Series2 = 'Other' AND PositionStatus2 = 'Filled', FTE2, 0) 
+ IIF(Not(IsNull(FTE3)) AND Series3 = 'Other' AND PositionStatus3 = 'Filled', FTE3, 0) 
+ IIF(Not(IsNull(FTE4)) AND Series4 = 'Other' AND PositionStatus4 = 'Filled', FTE4, 0) 
+ IIF(Not(IsNull(FTE5)) AND Series5 = 'Other' AND PositionStatus5 = 'Filled', FTE5, 0) 
+ IIF(Not(IsNull(FTE6)) AND Series6 = 'Other' AND PositionStatus6 = 'Filled', FTE6, 0) 
+ IIF(Not(IsNull(FTE7)) AND Series7 = 'Other' AND PositionStatus7 = 'Filled', FTE7, 0) 
+ IIF(Not(IsNull(FTE8)) AND Series8 = 'Other' AND PositionStatus8 = 'Filled', FTE8, 0) 
+ IIF(Not(IsNull(FTE9)) AND Series9 = 'Other' AND PositionStatus9 = 'Filled', FTE9, 0) 
+ IIF(Not(IsNull(FTE10)) AND Series10 = 'Other' AND PositionStatus10 = 'Filled', FTE10, 0) 
) As cntOther
FROM tblGeneral LEFT JOIN tblStaff ON tblGeneral.ChartID = tblStaff.ChartID

Open in new window

0
 
JLohmanAuthor Commented:
The backend is MSAccess.

Unfortunately, your suggested code is still returning all 0.00s.

I am using the following to display the results:

      Response.Write "<TD class='CCTable' width='75'>" & FormatNumber((cnt1411),2) & "&nbsp;</TD>" & VbCrLf
0
 
aikimarkCommented:
Are you querying the same set of data you posted in this thread?
0
 
JLohmanAuthor Commented:
Yes. The database is the same data.
0
 
JLohmanAuthor Commented:
THANK YOU!!! Works perfectly for all three variables.
0
 
JLohmanAuthor Commented:
Thank you. Code works perfectly for all three variables.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.