5 Replies Latest reply on Jan 27, 2010 9:46 AM by philmodjunk

    How to create a calculation that counts records by variables; IDStudent, code and date

    tzaluski

      Title

      How to create a calculation that counts records by variables; IDStudent, code and date

      Post

      Problem:

      I need to create a field that will count the number of each attendance code in one day for one student,

      then calculate if it is a Day_Late or Day_Early and have it included in the SumCode field for that

      code. 

      Everything works fine now, however we are replacing Day Early and Day Late with SignIn And SignOut.

      Then using the same Attendance report which is a subsummary on IDStudent 

       

      Attendance codes:

      Day_Absent

      (Day_Late)

      (Day_Early) 

      Sign_In

      Sign_Out 

       

      How many SignIn and Signout codes on each day for one student within the found set.

      Then calculate whether the formula would result in a Day_Late, Day_Early or nothing.

       

      Example:

      1/1/2009  ID1500   signin          

      1/1/2009  ID1500   signout

      1/1/2009  ID1500   signin

       

      1/2/2009  ID1500   signout

       

      1/15/2009 ID1500  signin

       

      1/22/2009 ID1500  signout 

      1/22/2009 ID1500  signin 

       

      the result for the found set:

      1/1/2009  ID1500 DayLate    (Signed in Late, Signed out for appointment (DayEarly), signed back in again from appointment)   

      1/2/2009  ID1500 DayEarly

      1/15/2009 ID1500 DayLate

      1/22/2009 ID1500 nothing 

       

      Attendance sheet would show (sumsummary report on IDSTUDENT)

      Day Late      2

      Day Early    1 

      Day Absent   0 (this works fine) 

        • 1. Re: How to create a calculation that counts records by variables; IDStudent, code and date
          philmodjunk
            

          Is the following logic correct?

           

          If student "signs in" one more time than they sign out on a given day, they're "DayLate".

          If student "signs out" one more time than they sign in on a given day, they're "DayEarly".

          If sign ins = sign outs for a given day, nothing.

           

          If so, make sure the above logic applies to every possible combination of records and report back. I can then suggest an approach that should work for you if I've got the logic down correctly.

          • 2. Re: How to create a calculation that counts records by variables; IDStudent, code and date
            tzaluski
              

            The only one that doesn't apply across the board is:

             If sign ins = sign outs for a given day, nothing.

             

            In the example the student "signed out" then "signed in", which equaled 0. They came back.

            However, if the student "signed in" (Day Late) then "signed out" (Day Early), it would be both Day Late and Day Early. 

            I should have made an example of that scenario as well. 

            • 3. Re: How to create a calculation that counts records by variables; IDStudent, code and date
              philmodjunk
                 Please post an example that includes that detail. I'm having trouble seeing how you distinguish each category of results just from the data shown.
              • 4. Re: How to create a calculation that counts records by variables; IDStudent, code and date
                tzaluski
                  

                Example:

                1/1/2009  ID1500   signin          8:15 am

                1/1/2009  ID1500   signout        12:00 pm

                1/1/2009  ID1500   signin           1:00 pm

                 

                1/2/2009  ID1500   signout        2:00 pm

                 

                1/15/2009 ID1500  signin         12:15 pm

                 

                1/22/2009 ID1500  signout        10:00 am

                1/22/2009 ID1500  signin          12:00 pm

                 

                1/25/2009 ID1500 Signin           9:00 am

                1/25/2009 ID1500 SignOut         1:45 pm

                 

                the result for the found set:

                1/1/2009  ID1500 DayLate    (Signed in Late, Signed out for appointment (DayEarly), signed back in again from appointment)   

                1/2/2009  ID1500 DayEarly   (Signed out for early dismissal - an appointment and did not return) 

                1/15/2009 ID1500 DayLate   (Signed in late)

                1/22/2009 ID1500 nothing    (Signed out for an appointment, signed back in 2 hrs later.... )

                1/25/2009 ID1500 DayLate and DayEarly  (Signed in Late at 9:00 am and then signed out for early dismissal and did not return at 1:45pm) 

                 

                Attendance sheet would show (sumsummary report on IDSTUDENT)

                Day Late      3

                Day Early     2

                Day Absent   0 (this works fine) 

                 

                I amended the instances listed earlier to include 1/25/2009. This shows where sign ins = sign outs for the same day would not = 0 code, but both DayLate and DayEarly. As you can see I added times left, which are  not recorded anywhere except by comment.  I hope this helps. 

                 

                • 5. Re: How to create a calculation that counts records by variables; IDStudent, code and date
                  philmodjunk
                     I don't see any way to do this without adding a time field to record the time of the SignIn/SignOut events. Otherwise there's no way for the database to interpret the information consistently.