4 Replies Latest reply on Aug 20, 2010 2:53 PM by philmodjunk

    A simple counting problem..probably



      A simple counting problem..probably


      Hello All

      fairly new to FM Pro11 (from Access) and I'm having a wee problem.

      i have a table with the fields: date, specialty, operation

      what i would like is a way of counting the number of occurences of each specialty within a given time period. i get how to constrain the date but all i get is a record for each date within the period and not a single record with the total. am i missing something very simple?

      thanks in advance


        • 1. Re: A simple counting problem..probably

          Sounds like you want a summary report with just one summary row for each specialty even though you have many records for each specialty. THat correct?

          Do you know how to create a summary report?

          If so,

          1. Define a "count of" summary field
          2. Create a layout based on your table.
          3. Change the Body layout part in to a Sub summary part sorted on specialty.
          4. Put your summary field in this part along with date and specialty.
          5. Enter browse mode, find the records in the date range you want and then sort your records by specialty to group them together.


          For more on summary reports:  Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: A simple counting problem..probably


            thanks for your help.

            Actually the table has more fields than those originally listed but i just wanted to sum the specialty field. I have a separate related table for specialty and this has a count field in it which returns the total number of specialty counts in the other table but i cannot for the life of me get the numbers for any date period.

            i've tried your method (i think) and that just gives me the total numer of specialty occurences (the number of times the field has been occupied) but not the number of the different types of specialty. i'm not explaining this very well so ;

            2 tables liked by;  Patients::Specialty  =  Surgical specialty::specialty

            with the count field in the 2nd table being   Count(Patients::specialty)

            the date only exists in the Patients table

            btw the summary field you suggested is for count of specialty?


            • 3. Re: A simple counting problem..probably


              i've just read the last part about sorting and guess what......thanks so much


              • 4. Re: A simple counting problem..probably

                If you place the field in a sub summary layout part with no body, ( this is one of the latter "tricks" toward the end of the article whose link I posted ) you'll get a subtotal for each type of specialty if you set your layout up for this type of grouping. The counting summary field can refer to any field in the table that is never empty and you'll get the same result.

                You can also define a different relationship between Surgical Specialty and patients that includes addtional field pairs so that this relationship matches both by specialty and also by a pair of dates entered in global date fields in the Surgical Specialty table. A Count function that refers to this new table occurrence will give you the same totals for a report based on the Surgical Specialty table.