Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sum of multiple fields in one record based on multiple criteria

Posted on 2012-08-14
31
Medium Priority
?
412 Views
Last Modified: 2012-08-27
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
0
Comment
Question by:JLohman
  • 14
  • 7
  • 5
  • +2
29 Comments
 
LVL 9

Expert Comment

by:joaoalmeida
ID: 38294574
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
 

Author Comment

by:JLohman
ID: 38294688
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
 
LVL 41

Expert Comment

by:ralmada
ID: 38296319
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:JLohman
ID: 38296925
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
 
LVL 41

Expert Comment

by:ralmada
ID: 38297276
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
 

Author Comment

by:JLohman
ID: 38297309
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
 
LVL 41

Expert Comment

by:ralmada
ID: 38297361
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
 

Author Comment

by:JLohman
ID: 38298217
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
 
LVL 41

Expert Comment

by:ralmada
ID: 38300293
so what's the issue with them? Can you post some sample data and the expected result?
0
 

Author Comment

by:JLohman
ID: 38300953
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
 
LVL 41

Expert Comment

by:ralmada
ID: 38303481
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
 

Author Comment

by:JLohman
ID: 38306677
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
 

Author Comment

by:JLohman
ID: 38326512
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
 
LVL 9

Expert Comment

by:joaoalmeida
ID: 38326653
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
 

Author Comment

by:JLohman
ID: 38330625
It is still returning all values of '0'
0
 

Author Comment

by:JLohman
ID: 38332634
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
 
LVL 31

Expert Comment

by:hnasr
ID: 38337197
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
 
LVL 46

Expert Comment

by:aikimark
ID: 38337294
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
 
LVL 46

Expert Comment

by:aikimark
ID: 38337317
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
 

Author Comment

by:JLohman
ID: 38338345
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
 
LVL 46

Expert Comment

by:aikimark
ID: 38338383
my bad.  I copy/pasted the lines and change & missed the PositionStatus# differences. :-(
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38338387
Please confirm that the back-end database is MS Access
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38339034
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
 

Author Comment

by:JLohman
ID: 38339155
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
 
LVL 46

Expert Comment

by:aikimark
ID: 38339165
Are you querying the same set of data you posted in this thread?
0
 

Author Comment

by:JLohman
ID: 38339244
Yes. The database is the same data.
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 38339252
shouldn't cnt1411 be qualified with the recordset name?

I expected something like rs!cnt1411
0
 

Author Comment

by:JLohman
ID: 38339318
THANK YOU!!! Works perfectly for all three variables.
0
 

Author Closing Comment

by:JLohman
ID: 38339320
Thank you. Code works perfectly for all three variables.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question