3 Replies Latest reply on Feb 11, 2009 12:28 PM by ninja

    Sum on All Records with Common Field

    TSADesign

      Title

      Sum on All Records with Common Field

      Post

      I am trying to do a sum on all records that have a common field by matching a different field.

       

      Where "Month" is a field that will contain a month for the year, and "Months for Report" is a drop down list with January - December values.

       

      I have tried using an =if statement as follows:

      If ( Month = Months for Report ; Sum (Sales) ; False )  

       

      The idea here is that when a month is selected in the drop down list "Months for Report", that it would give the sum of all fields with the "Month" field equaling that value. For example, if the report contains "Sales" for January, February, or March, then if I were to select 'January' in the "Months for Report" drop down, then the sum of "Sales" for records with the value 'January' would be returned.

       

      I can get it to return only the value for the record that is presently selected if "Month" equals 'January', but I don't know how to get it to sum all of the records whose "Month" field equals 'January'.

       

      Any help would be appreciated,

        

        • 1. Re: Sum on All Records with Common Field
          ninja
            

          Howdy TSADesign,

           

          Please don't take the shortcut personally, but I'm working with another on this same topic currently.  You may want to check it out here:

           

          Script that looks at two files

           

          The Topic name doesn't lead you to believe it's the same...but it is.  Don't sum a found set...sum a tagged set via relationship.

           

          Let us know if you think it's the same or not.

          • 2. Re: Sum on All Records with Common Field
            TSADesign
              

            Thank you Ninja for the reply. I have looked at the other link and will try to see if I can make this work.

             

            One difference I see is that I am doing all within the same table, but two different layouts. It does sound very similar though.

             

            In my Layout 1, I have a field named "Month" that will be populated with the correct month for the data imported. So if I enter a value in another field that equals the value in Month, then can I do the sum based upon matched values - might look something like this =sum(months for report) + sum(months) 

             

            I tried this but it didn't work, but am I getting close? 

            • 3. Re: Sum on All Records with Common Field
              ninja
                

              howdy TSADesign,

               

              You're all in one table...but you can have multiple Table Occurrences.  Join T.O. Table1(old) with T.O. Table1(new) by the uniqueID AND by linking the tagging field to the global field (again referring to the other thread).  It will work exactly the same way.  That way you just need:

               

              = Sum(Table1(new)::amount)  {note that from Table1(old) I'm Summing from Table1(new).  It's important.}