5 Replies Latest reply on May 28, 2012 10:40 AM by taylorsharpe

    Calculating total absences week by week

    jbrown

      Hey all,

      In my school system I have a table for attendance. The teachers create records in this table. I need to show the number of absences each week of a trimester.

      Right now the only thing I can think of doing is having one calc field for each week of the trimester that returns a 1 if the attendance record was created during that week of the trimester. SO I'd have 12 calc fields, one for each week. In the school table, there's a corresponding field that sums the total count for each week, again 12 fields.

       

      Is there a better way to get the total number of absences each week of a trimester?

       

      RIght now this resets each trimester change. So I only have attendance for the current trimester. If i wanted to keep track of the whole year, would I need 36 fields: one for each week of the year?

       

      Help me.If you have any advice i'd love to know.

      thanks

      jb

        • 1. Re: Calculating total absences week by week
          DABrant

          Jeremy,

           

          If you incluse a text field identifying the trimester using a simple consistent code such as a four digit year plus a trimest indicator, e.g. 2012T1, you could the use only 12 calculation fields plus a compound key to keep track of every weekof every trimeseter in your database.

          • 2. Re: Calculating total absences week by week
            taylorsharpe

            If the trimesters are equally spaced throughout the year, you could do a calcluation based on the date.  But somehow I doubt the school system exactly breaks evenly spaced trimesters.  I would probably create a separate table indicating the ending date of each trimester and have a relationship with the date that looks for the next trimester ending date and give the corresponding trimester field, which could be as DABrant says, something like "2012T1" or whatever works for you.

             

            FM3.jpg

             

            If you'll email me, I'll send you this Attendance.fmp12 file. 

            • 3. Re: Calculating total absences week by week
              jbrown

              Thanks DABrant & Taylor.

              Its a beautiful day here in COlorado. I had to get outside a bit.

               

              I'll certainly need to study what you guys have suggested.

               

              I forgot to mention  that I have the 12 calc fields because i'm charting the total of each week.

              Right now it resets at the beginning of each trimester.

              But I'll study what you've suggested.

               

              Let me get back to you.

              Thanks

              jb

              • 4. Re: Calculating total absences week by week
                taylorsharpe

                Better yet, have a calculation that adds the week to Trimester and Week together so you can group them for your chart such as "2012T1.03" would be the First Trimester of 2012 and be week #3.  Here is how it would work.  The bottom part shows Attendance where you just put in the person's ID and the date, and on the far right, it puts the Trimester.Week calcluation.  You can then chart the Trimester.Week easily. 

                 

                FM4.jpg

                • 5. Re: Calculating total absences week by week
                  taylorsharpe

                  Ooops calculation Error.  Right now Days 1-7 would be week 0 and days 8-14 would be week 1, like we do with Age.  But for attedance, you probably want Day 1-7 to be week 1.  So here is the calculation correction:

                   

                  FM5.jpg