3 Replies Latest reply on Aug 15, 2013 2:26 AM by hrcap

    Total Days Off since the start of the year

    hrcap

      Title

      Total Days Off since the start of the year

      Post

           Evening All

            

           I require some assistance if possible please.

            

           I have a database of employees (Table name: EMPLOYEES), i can add time off that any employee has had as one of the following (in a table titled: ABSENCES):

           Accident

           Holiday

           Sickness  (each of these three are stored in a field titled: Type)

            

           For each of the time off records i have a field titled: total_weekdays_off that calculates the total number of weekdays that the employee has missed due to that particular absence.

            

           On each of the employee files i am looking to three fields that gives a calculation for the total number of weekdays that have been taken off in relation to Accidents, Holidays, and Sickness since 01/01/CurrentYear, but i cannot work out how to do this.

            

           Any help would be appreciated.

            

            

           Cheers

            

           Hadleigh

        • 1. Re: Total Days Off since the start of the year
          philmodjunk

               And in what format do you need to present this information? A summary report could be setup on a layout based on absences where you see the employee's name followed by three subtotals--one fore  each absence type, followed by the over all total:

               John Smith

                    accident:  1
                    holiday:    3
                    Sickness:  5

               Total: 9

               Jane Jones

                     holiday: 2
               and so forth....

               Does that work for you?

          • 2. Re: Total Days Off since the start of the year
            EricBrown

                 Hi Hadleigh,

                 I might be able to help with that one...

                 Your joined table ABSENCES is tracking each Employee's absence with a new record dated for the absence. To calculate the total for the year you want to build a summary field or a calculation field in the EMPLOYEE record that finds related records in ABSBENCES and gives you a total. Your calculation will filter for type of absence and date. It's similar to doing a subtotal on an invoice based on invoice data:

                 If ( IsEmpty ( Invoice Data::INVOICE ID MATCH FIELD ) ; 0 ; Sum ( Invoice Data::Amount ) )

                 Yours might be a little more complicated and look something like this:

            AccidentTimeOff =

                 If ( ABSENCES::fK_EmployeeID = K_EmployeeID and ABENCES::Reason = "Accident" and AbsenceDate > beginning of year ; Sum ( ABSENCES::TIMEOFF ) ; 0 )

            SickTimeOff =

                 If ( ABSENCES::fK_EmployeeID = K_EmployeeID and ABENCES::Reason = "Sickness" and AbsenceDate > beginning of year ; Sum ( ABSENCES::TIMEOFF ) ; 0 )

            HolidayTimeOff =

                 If ( ABSENCES::fK_EmployeeID = K_EmployeeID and ABENCES::Reason = "Holiday" and AbsenceDate > beginning of year ; Sum ( ABSENCES::TIMEOFF ) ; 0 )

            total_weekdays_off = AccidentTimeOff + SickTimeOff  + HolidayTimeOff 

                 This will vary a little depending on your field names and how you want to track the year, but should do the job for you. 

                  

            • 3. Re: Total Days Off since the start of the year
              hrcap

                   Hi guys

                    

                   thank you very much for your help it was very useful

                    

                   cheers

                    

                   Hadleigh