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.
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.
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...
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.
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.
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.
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.
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!!!!!