6 Replies Latest reply: Mar 17, 2011 12:37 PM by Matt Cayford RSS

    Totalling various columns from within concatenated link tables

      Hi all,

       

      I hope someone can make sense of this.

      I have expression 1 which is totalling various numbers from our ODBC database:

       

      SUM(IF(Week_Class ='Current Week',(basichours+nextdayhours+othours+nextdayot+salaryhours)))

      This works fine . I want to be able to add some columns to this grand total from table files. I have manually entered these table files and concatenated into a link table.

      These are also working fine on their own:

      expression 2

      SUM(IF(Week_Class ='Current Week',(MitchPerm+MitchBank+MitchAgy+GarPerm+GarBank+GarAgy+HomeCarePerm+HomeCareBank+HomeCareAgy)))

       

      But if I try to add the above two sets of columns together in 1 sum if expression, I get 0.

      The columns in expression 2 do not have corresponding values in the columns in expression 1 so I think this is where its falling down.

      Is there something I can state in the expression such a distinct command which will sum the applicable columns together.

      Unfortunately, I cannot post sample data due to the vastness of this database. There are a lot of linked tables but I 'think' this is a syntax issue rather than relationship (?) as the 2 expressions are working seperately.

      Any help would be gratefully appreciated.

       

      Matt