2 Replies Latest reply on Jan 26, 2011 10:11 AM by philmodjunk

    How to sum a value in records with duplicate fields

    DarrenReiter

      Title

      How to sum a value in records with duplicate fields

      Post

      I'm crossing over from Access and I could use some help trying to do the following in Filemaker:

      I have a database with several fields with the last field containing a numerical value.  The first two fields have many duplicates and where those duplicates exist, I would like to sum the values in the final field.

      For example:

      Field1 Field2 Field3

      a b 10

      a b 5

      d e 20

      d e 5

      Becomes:

      Field1 Field2 Field3

      a b 15

      d e 25

        • 1. Re: How to sum a value in records with duplicate fields
          Sorbsbuster

          Create a calculation field, result text:

          FieldsBoth= Field1 & "-" & Field2 (for example)

          Create a summary field = Total of Field3

          On the layout create a sub-summary part, defined as summarising when sorted by FieldsBoth, and put that summary field into it, along with the Field 1 and Field 2, or FieldsBoth, whichever suits.

          Get your found set and sort by FieldsBoth.  You can remove the body section of the report if you want, so it only shows the sub-summary lines.

          Note this assumes that records containing a's and b's are always entered as Field1= a and Field2 = b.  a-b will be different from b-a.

          • 2. Re: How to sum a value in records with duplicate fields
            philmodjunk

            You can actually do this without the calculation field.

            Make your sub summary part "when sorted by Field 2"

            Then sort your records first by field 1, then by Field 2.

            The same assumptions srbsbuster has made apply to this approach as well.