2 Replies Latest reply on Aug 4, 2013 4:13 PM by bootht

    Calculation advice

    bootht

      Title

      Calculation advice

      Post

           Hello, Please please please can someone help, I have been searching the internet for advice for hours!

           I have bought a training DVD but it did not demonstrate the calculation I needed either.

           I have created a fairly simple relational database for the Special Needs School in which I work, which logs behavioural incidents. Main tables are 'Students' - 'Generic Incidents' - Serious Incidents'

           I need to create a calculation to count the number of incidents that occur each month, one to calulate generic incidents by month and one for serious incidents by month. The date field is simply called 'Date of Incident' which logs an incident day/month/year.  I need to create a report for Governors on incidents by next week aarrghg!

           Please can anyone help

           Thanks a lot 

           Tom

      Screen_Shot_2013-07-01_at_23.20.26.png

        • 1. Re: Calculation advice
          philmodjunk

               I'd use one table for all incidents whether generic or serious. I can guess that a lot more data has to be recorded when the incident is serious, but I'd still use a single table for that data. You can either define additional fields for the serious incidents that are just left empty for generic ones or you can link in a related table for the serious incidents only, but link that to the incidents table.

               This then allows you to set up a summary report which can be broken down into sub totals by month and by type of incident.

               the following calculation field is useful for giving all records with a date in the same month/year, a common value so that you can sort to group them by month:

               cMonth: IncidentDate - Day ( IncidentDate ) + 1

               See this thread to learn more about summary reports: Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Calculation advice
            bootht

                 Thank you so much for the reply. I thought about having just one table for all incidents when I first planned the database, but decided to use 2 when I actually created it. I will change it to 2 and do some testing.

                  

                 thanks again for your help.

                  

                 Tom