8 Replies Latest reply on Nov 6, 2013 2:04 PM by DonnaShelton

    Help with calculation



      Help with calculation


           I need to create a calculation and have done so before but cannot figure out how to do so for what I am needing.  I'm creating a database to keep up with vacation and sick leave time.  When the vacation/sick leave form is filled out and the number of hours is entered, I want it to subtract from the amount I currently have and give me the updated amount.  I also want it to add the vacation (11 hrs) and sick time (8 hrs) that I get on the first of each month. (Ex:  If I am taking 8 hours of vacation and have a total of 50, I want it to subtract 8 from 50 and give me 42.  Then on the first of the month, I want it to add back 11 hours for a total of 53 hours).  Any help would be greatly appreciated.


        • 1. Re: Help with calculation

               You'll need to describe the fields, tables and relationships you have in much more detail before we can offer a suggestion consistent with what you currently have in place.

          • 2. Re: Help with calculation

                 I have a fields of Date ("Todays_Date") , begin date ("Date") and end date ("Date _2), begin time ("Hours_From" & "Minutes_From") to end time ("Hours_to" & "Minutes_to").  I then enter "Total_hours".  I also have check boxes that read: "Vacation", "Sick", "Comp Time", & "Jury Duty".    It looks something like this:

                 Today's Date:     (Todays_Date)

                 Date:    (start)                                 (end)                                  .                                                                   Reason:             Vacation       (Ck box)

                 Time:   (Hour):Minute    (AM/PM)  -  (Hour):(Minute)  (AM/PM)  (All of these are drop down)                                        Sick               (Ck box)

                 This totals  (This_totals)   working hours                                                                                                                             Comp Time (Ck box)

                                                                                                                                                                                                                       Jury Duty       (Ck box)

                 There are other things (places for signatures) but nothing that would apply.


            • 3. Re: Help with calculation

                   And what does this represent? Does one record represent one request for leave by one employee?

                   Is it linked to a table of employees?


                        I want it to subtract from the amount I currently have

                   That starting leave allocation would seem to be a field in such a related employee record...

              • 4. Re: Help with calculation

                     Currently it is my own record.  If I can get it to work correctly, it will be used for multiple employees.  I have also added fields, "Vacation_hours_available", "Sick_hours_available", Monthly_vac_hours_added", "Monthly_sick_hours_added".  The vac/sick hours added would be what is added each month and the sick/vac hours available is where I want the correct amount of hours to be calculated to.

                     Is this way too confusing??  That's probably why I can't figure it out.

                • 5. Re: Help with calculation

                       Sorry, but you really didn't answer my question. Will one record in this table represent a single leave request by a single employee?

                       Is there a related table of employees?

                       I could describe something here that works for just your leave requests using just this one table, but then it would not be something easily scaled up to work with requests by multiple employees.

                  • 6. Re: Help with calculation

                         I'm sorry for not answering your question.  Yes, one record does represent a single leave request by a single employee.

                         There is not a related table of employees.

                         Right now I want it to work for me and then I'll go from there if I need to.

                         You have no idea how much I appreciate your help!  My go to person retired from the university and now I'm trying to figure it all out by myself.


                    • 7. Re: Help with calculation

                           But "going from there" could be a problem if we don't keep ultimate objectives in mind from the start. A related employee table can be used to store the allocated leave time for each employee. When setting this up for yourself, you could use global fields to store that data, but that won't work for more than a single person.

                           And you'll need some kind of system that updates your totals in those fields or employee record each month.

                           Let's set this up for sick leave and leave it to you to generalize the method for handling vacation time the same way.

                           Define a global number field: gAccumulatedSickLeave. Manually enter your current leave in hours into this field.

                           Define a calculation field, cReqSickHrs as:

                           If ( Not IsEmpty ( SickLeaveCheckBoxFIeld ; ( GetasTime ( Hours_To & ":" & Minutes_To ) - GetAsTime ( Hours_From & ":" & Minutes_From ) ) * 60 )

                           to compute the requested Sick leave hours. This assumes that your SickLeaveCheckBoxField is a single field with a single checkbox.

                           Define a summary field, sTotalSickHours as the "total of" cReqSickHrs.

                           Define cRemainingSickHrs as:

                           gAccumulatedSickLeave - sTotalSickHours

                           And the simplest script for updating gAccumulatedSickLeave each month is this script step:

                           Set field [YourTable::gAccumulatedSickLeave ; YourTable::gAccumulatedSickLeave + 8 ]

                           You could manually perform that script once a month.

                           And there are more sophisticated scripts that can be set to run every time that you open the file that check today's date, compare it to the date the total was last updated (a date stored in another global field) to correctly update the total in the field.

                           Please note that this solution assumes that the file is stored on your computer. It will not work as designed if you host it over the network and connect to it as a client of the database.


                      • 8. Re: Help with calculation

                             Ok, it's getting late and my brain is tired of working on this.  I'll try it tomorrow and see what happens.

                             Again, thank you SO much for your help!!!!!