6 Replies Latest reply on Jun 30, 2011 2:46 PM by David40

    Summarizing Records



      Summarizing Records


      I am running FMP 9 on an iMac computer (Snow Leopard).

      I have asked this question before, but I believe I may have asked it incorrectly.  I apologize for any confusion.

      I have a historical research database that tracks events by date.  I would like to create a summary report that totals the number events per day in any given month.  Presently, I have to run a daily report (31 times) to get the total for each month.  The date is separated into three individual fields (month-day-year).  The output report would show (for example) January 1, 200 events, January 2, 150 events, etc.

      Thanks in advance!


        • 1. Re: Summarizing Records

          Presumably, you want your groups of events in chronological order first by year, month and then by day within the group for each month.

          You first need date fields that you can sort on so that such an order and grouping is possible.

          Let's start with a date field for each event in place of your three separate fields for month, day, year. Such a field is needed for correct sorting and grouping here.

          I don't really see a purpose in splitting your dates into three fields, but if you insist on that format, the first issue is to use them in a calculation to produce the actual date in a date field. I'll call this field cEventDate and define it with this expression:

          Date ( Ceiling ( Position ( Left ( Month ; 3 ) ; "JanFebMarAprMayJunJulAugSepOctNovDec" ; 1 ; 1 ) / 3 ) ; Day ; Year )

          Make sure that Date is the data type returned by this calculation field.

          Sorting our records by cEventDate in ascending order will sort all your records in proper chrnological order.

          Now we need a field we can use to group together all records from the same month and year. We'll call this field cMonth:

          cEventDate - Day ( cEventdate ) + 1  // this calculation returns the date of the first day of the month indicated in cEventDate

          Make sure that Date is the return type for this calculation field.

          Now you can sort your records frist by cMonth, then by cEventDate to first group by month then sort with in each monthly group in chronological order.

          Now for your various counts in your report.

          Define a summary field sCount as the "Count Of" any field in your database that is never empty such as cMonth.

          Place two sub summary layout parts on your layout.

          Set up the first one as "when sorted by" cMonth to show the monthly totals. Put sCount in this part to show the total count for the month. You can add cMonth to this part and format it to display the month name and the year.

          Set up the second sub summary part as "when sorted by" cEventdate. Put sCount in this part to show the daily total and you can add cEventDate to this layout part and format it to show the month name and day if you want.

          Delete the body layout part as you won't need it for this report.

          Now do a find to find the records you want for your report, then sort them by cMonth, then by cEventDate and you'll get the report you've specified here, provided you view it in list view.

          • 2. Re: Summarizing Records

            Thank you for your response!  I apologize for the delay in asnwering.

            Although your solution is probably a good one, it is beyond my capabilities and understanding.  I may again have asked the question incorrectly.

            I separated out the date into three fields so I could sort by various combinations of month, day, or year.  For example, if I wanted to see how many people were born in the month of January, I can produce a report (it may be several hundred pages, but I can do that).  Presently, if I run an inquiry of what events occurred on June 29th, it produces a report with a count of the events.  

            What I would like is to run a simple report that tells me number (count) of events that occur on each day of the year.  The year they occurred in is not applicable.  Nor, do I need to see which events they are.  

            As I mentioned before, I presently have to run my Month-Day Report 366 times to get the information I want.  

            Thank you for being patient and understanding.


            • 3. Re: Summarizing Records

              The method I describe produces what you are requesting and you can use calculation fields that extract your month, day etc from the date field--which makes for easier data entry--as you can just enter the date, but see the separated day, month etc appear in the calculation fields. (and some of these queries can be done by entering criteria directly in the date field rather than one of these separated fields.

              However you manage it, you really need a field of type date storing the date in order to get the results you want.

              I can take you through the above process in smaller steps if you want, just let me know where you first get lost and I'll take another swing at it from there.

              • 4. Re: Summarizing Records

                Thank you again for your suggestion and patients!

                I followed your suggestion (sort of), but arrived at the results I was looking for (sort of).  I sorted my database by month and day.  I then added a count as a summary field sorted by "day".  The output report for the month of January is over 400 pages long.  However, after each day, I get a summary number.  In about 5 minutes (or less if not bothered by cats), I was able to note the number of entries of I have for each day of January.  Previously, that would have taken me over an hour. 

                You have solved a problem I have been struggling with for over a year.  Now if I could just reduce the report to one line per day, that would be even better.

                Any suggestions?  Is there a way to run the report have the summary data saved someplace else?

                Thanks again!


                • 5. Re: Summarizing Records

                  You can reduce your report to one line per day if you remove the body layout part and just keep the sub summary part.

                  • 6. Re: Summarizing Records

                    Super Duper!  This is just what I wanted!  I have been struggling for a couple of years trying to get this result.  You have made it seem far too easy.  Thank you so much!