Paid time off database

[roger556]

Hello,


I am looking to build a database tracking paid time off of around 100 employees.Please any suggestions or advise is more than welcomed.
I have created tables for managers, employees,shift,part time full time.
I needed suggestions for how to create table for recording and calculating /PTO time for all the employees.
Thanks in advance

Roger

[jzwp11]

First, all people (manager, non-managers and anyone else) should be in 1 table.

Is the amount of PTO per employee dependent on years of service or job title/position?
Is the amount of PTO dependent on whether the person is full-time or part-time?
Do you want to track changes in PTO over time?
Do you need to track changes in an employee position/title over time?
Do you need to track changes in an employee's work status (part of full time) over time?
Does the shift a person work have any impact on PTO?
Are there min/max amounts of PTO an employee can take in one event?

[roger556]

Hello
Thanks a lot for your response.The answer to all your question is yes also I have all the managers employees all under one table.Actually I am importing the data from a excel spreadsheet.
Please any suggestions is more than welcomed.

Roger

[roger556]

Hello,
Attach is the copy of the database model that I have created.What I am missing is table for PTO something that will incorporate all the questions that you have presented in your reply.

Roger

[jzwp11]

I'm having trouble following your table structure. First, the employee table should have just basic information about the employee. All employees should be in this table (no need for a manager table)

tblEmployee
-pkEmployeeID primary key autonumber (pk=primary key)
-txtFName (txt=text field)
-txtLName
-dteHire (date of hire) (dte=date/time field)

We would also need a table that holds all possible positions (as you already have). This would include various job identifiers such as manager, supervisor, employee, plumber, electrician etc. Now since you said that PTO is dependent on the position, we should have a field to hold that contribution to PTO that is relevant to the position (I'm not sure what units of measure you use, I'll assume days).

tblPositions
-pkPositionID primary key, autonumber
-txtPosition
-longPositionPTOAllowance (PTO attributable to the position)

It looks like you also have different sites (so a table for that).

tblSites
-pkSiteID primary key, autonumber
-txtSiteName

Can an employee work at different sites?

What do you mean by hiring status?

You said you want to track an employee's time status (full or part time) over time but your structure does not show that. Essentially a person can have many time statuses over time which is a one-to-many relationship. Further, a certain status can apply to many employees (many employees can have a full-time status), so another one to many relationship. When you have 2 one-to-many relationships between the same two entities (employee and time status in this case), you have a many-to-many relationship which is handled with a junction table.


I would probably have a table that holds all possible statuses (2 in your case that I know of: full time and part time) as records. You can add more if needed later. Since you also said that PTO is also dependent on the full or part time status of an employee, we need a field in the following table to hold that as well.

tblTimeStatus
-pkTimeStatusID primary key, autonumber
-txtTimeStatus
-longTimeStatusPTOAllowance

Now for the junction table

tblEmployeeTimeStatus
-pkEmpTimeStatusID primary key, autonumber
-fkEmployeeID foreign key (fk=foreign key, must be a long number integer field) to tblEmployees
-fkTimeStatusID foreign key to tblTimeStatus
-dteTimeStatusEffective (date when the status for the employee became effective)

Any time a person's time status changes, you would add a new record in the above table. The one with the most recent date would give their current status.

You would use the same approach if an employee moved from site to site or position to position

tblEmployeeSite
-pkEmpSiteID primary key, autonumber
-fkEmployeeID foreign key to tblEmployees
-fkSiteID foreign key to tblSites
-dteSiteEffective

tblEmployeePosition
-pkEmpPositionID primary key autonumber
-fkEmployeeID foreign key to tblEmployees
-fkPositionID foreignk key to tblPositions
-dtePositionEffective

Now you have to relate people or more correctly you have to relate the people and their position to other people and their positions (worker to manager). For example, Joe is a manager and he has Tom the plumber and Bill the electrician reporting to him. Of course the mix of employees that report to a manager can also change over time, and I assume that you want to track that as well.

tblEmployeePositionRelate
-pkEmpPositionRelateID primary key, autonumber
-fkPEmpPositionID foreign key to tblEmployeePosition
-fkSEmpPositionID foreign key to tblEmployeePosition
-dteEmpPositionRelateEffective

I used P and S to distinguish the two foreign keys since you cannot have two fields in a table with the same name. You could say that P represents the primary (manager) while the S represents secondary (someone who reports to the manager).

Now if an employee's position and site are inter-related that might change the structure I have presented, but that depends on your application.

In terms of shifts, are the employees and the manager they report to always on the same shift or can they be on different shifts?

For the PTO allowance based on time of service, you may need a table to hold the basic breakdown such as if an employee has 5 year or less of service, they would be entitled/allowed, 4 days of PTO/year, 5-10 years of service: 6 days of PTO/Year etc.

tblPTOService
-pkPTOServiceID primary key, autonumber
-longPTO

tblPTOYearsOfService
-pkPTOYearServiceID primary key, autonumber
-fkPTOServiceID foreign key to tblPTOService
-fkPeriodTimeID foreign key to tblPeriodTime
-longYear


For each PTO due to service, you would have a start and end (5 to 10 years) time period (one service to many time period limits)

tblPeriodTime (holds 2 records start and end)
-pkPeriodTimeID primary key, autonumber
-txtPeriodTime


Can you provide a little more detail/example about what and how you record PTO, units of measure etc.?

[roger556]

Hello,
First of all I would like to thank you for going through my database model and giving such valuable suggestions really appreciate it.
I understand my table was created in a very short notice and also since the data was imported from excel I did not want to mess up the structure and hence such descrepancies in the model.
"I am not sure what units of measure you use,I'll assume days"
Units of measure is in hours and it also depends on the pay grade and the number of years a employee has been with the company.
Previously we had different sites but now we are only just 2 sites.
Currently we have 2 types of employees full time and part time.
In terms of shifts managers are pretty much sceduled on the same time except of 2 supervisors who work evening shift and they work on alternate sundays.One manager works in the eveing shift.
"Can you provide a little more detail/example about what and how you record PTO,units of measure etc.?"
Our employees are divided among pay grade start from 1 going upto 9 and than couple of them have executive level pay grade.
Attach is the PTO eligibility chart hope this will help you in suggesting me the right table to conrtuct.

NON-EXEMPT JOB GRADES 1-6

Full Time Standard Weekly Hours 30 - <40Standard 20 - <30Standard 10 - <201st year of full service
based on the start date
8 hrs/pay
208 hrs max
26 day max
1.5 hrs/pay
39 hrs max

1.25 hrs/pay
32.5 hrs max

1 hrs/pay
26 hrs max

1-5 years8.32 hrs/pay
216 hrs
27 days3.5 hrs/pay
91 hrs max
2.25 hrs/pay
58.5 hrs max
1.95 hrs/pay
50.7 hrs max
6-9 years9.23 hrs/pay
240 hrs
30 days6 hrs/pay
156 hrs max
4.25 hrs/pay
110.5 hrs max
2.75 hrs/pay
71.5 hrs max
10-24 years10.77 hrs/pay
280 hrs
35 days
7.5 hrs/pay
195 hrs max
5 hrs/pay
130 hrs max
3.25 hrs/pay
84.5 hrs max
25+ years12.31 hrs/pay
320 hrs
40 days8.5 hrs/pay
221 hrs max
6 hrs/pay
156 hrs max
4 hrs/pay
104 hrs max

EXEMPT JOB GRADES 7-8

Full Time Standard Weekly Hours 30 - <40 Standard 20 - <30 Standard 10 - <201st year of full service
based on the start date9.23 hrs/pay
240 hrs max
30 days8.08 hrs/pay
210 hrs max
30 days5.77 hrs/pay
150 hrs max
30 days3.46 hrs/pay
90 hrs max
30 days1-5 years9.23 hrs/pay
240 hrs
30 days8.08 hrs/pay
210 hrs
30 days5.77 hrs/pay
150 hrs
30 days3.46 hrs/pay
90 hrs
30 days6-9 years10.77 hrs/pay
280 hrs
35 days9.42 hrs/pay
245 hrs
35 days6.73 hrs/pay
175 hrs
35 days4.04 hrs/pay
105 hrs
35 days10- 24 years11.38 hrs/pay
296 hrs
37 days9.96 hrs/pay
259 hrs
37 days 7.12 hrs/pay
185 hrs
37 days4.27 hrs/pay
111 hrs
37 days25+ years12.31 hrs/pay
320 hrs
40 days10.77 hrs/pay
280 hrs
40 days7.69 hrs/pay
200 hrs
40 days4.62 hrs/pay
120 hrs
40 days

EXEMPT JOB GRADE 9-12

Full Time Standard Weekly Hours 30 - <40 Standard 20 - <30 Standard 10 - <201st year of full service
based on the start date10.77 hrs/pay
280 hrs max
35 days9.42 hrs/pay
245 hrs max
35 days6.73 hrs/pay
175 hrs max
35 days4.04 hrs/pay
105 hrs max
35 days1-9 years10.77 hrs/pay
280 hrs
35 days9.42 hrs/pay
245 hrs
35 days6.73 hrs/pay
175 hrs
35 days4.04 hrs/pay
105 hrs
35 days10-24 years11.38 hrs/pay
296 hrs
37 days9.96 hrs/pay
259 hrs
37 days7.12 hrs/pay
185 hrs
37 days4.27 hrs/pay
111 hrs
37 days25+ years12.31 hrs/pay
320 hrs
40 days10.77 hrs/pay
280 hrs
40 days7.69 hrs/pay
200 hrs
40 days4.62 hrs/pay
120 hrs
40 days

EXECUTIVE

Full Time Standard Weekly Hours 30 - <40 Standard 20 - <30 Standard 10 - <201st year of service-based on start date12.31 hrs/pay
320 hrs max
40 days10.77 hrs/pay
280 hrs max
40 days7.69 hrs/pay
200 hrs max
40 days4.62 hrs/pay
120 hrs
40 days1 year through duration of employment12.31 hrs/pay
320 hrs
40 days10.77 hrs/pay
280 hrs
40 days7.69 hrs/pay
200 hrs
40 days4.62 hrs/pay
120 hrs
40 days

*Note: PTO accrual rate will be pro-rated for standard weekly hours less than 40. The accrual rate may include fractional rounding differences depending on which legacy company administers your payroll.

Scheduling and Reporting PTO
In general, PTO must be pre-approved and pre-scheduled with your supervisor, who will do his or her best to accommodate your time off requests, with consideration for scheduling, workflow and service demands within your team. Each business unit will establish a consistent set of guidelines for scheduling PTO. Your Manager will explain the details for your area.
Unscheduled absences
Unscheduled absences are sometimes necessary, but should be kept to a minimum. If you are absent when you are scheduled to work and you provide your supervisor with less than 48-hours notice, it will be considered an unscheduled absence. Excessive unscheduled absences may result in disciplinary action.



Carrying Over Unused PTO Days
If you have unused PTO at the end of a calendar year, you can carry up to five (5) days, or equivalent standard weekly hours, of this time as PTO for the new calendar year. At no time can you have more than five (5) carryover PTO days in a calendar year. Exempt employees must ensure that their PTO time is reported in MyHR as outlined above in order to carry over the time.
Change of Status
If you change your status during the calendar year (full-time to part-time or vice versa) your PTO going forward will be accrued at the new rate corresponding to your revised standard hours tier. This will take effect the pay period following the effective date of the change. If your hours change, you will begin to accrue PTO at the next standard hour tier on the next effective pay period following the effective date of the change.

PTO and Holidays
We are open to serve our Customers every day, with the exception of the following four:

• New Year's Day
• Easter Sunday
• Thanksgiving Day
• Christmas Day

If you are eligible for PTO and your normal schedule falls on one of these four holidays, but you are not scheduled to work, you are required to use PTO for that time. Your PTO allotment has been calculated to compensate for these holidays.
Although Stores are closed on these four holidays, some Employees may work because of the nature of their jobs. If you are a Non-Exempt Employee and scheduled to work Easter Sunday, Thanksgiving Day or Christmas Day, you will be paid time and a half for the hours that you work and your PTO balance will not change. If you are a Non-Exempt Employee and scheduled to work New Year's Day, you will receive regular pay for the hours that you work and your PTO balance will not change.
For other traditional holidays, such as July 4th, if you decide to take time off, or if your Manager does not schedule you to work, or if your department is closed, you are required to take PTO.
Your PTO should be scheduled in consultation with your Manager. If you have questions about PTO and holidays, speak with your Manager or HR Business Partner.

Please let me know if you have any other questions also in the meanwhile I will work on creating all the tables that you have suggested and will post it as soon as I am done.
Thanks for everything

Roger

[roger556]

Hello,
I tried to cut and paste our pto chart but unfortunately it did not post all the information actually the table is divided among 1 st year of service,
1-5,6-9,10-24,25+ years of service.
Also Full time, Standard weekly Hours 30<40,Standard 20-<30 and last Standard 10-<20 hrs.

Roger

[roger556]

Hello,
Attach you will find the model that I have created based on the information that you provided in your previous reply.Please let me know if it is correct also please let me know any corrections that I need to make so that this model works correctly.
I have a question how should I create a relationship between PTO services and employees?
Also I have made a small change , I have not created tblEmployeeSite as I do not have any employee that work cross site.

Thanks,
Roger

[jzwp11]

I did not see any attachments.

I have not created tblEmployeeSite as I do not have any employee that work cross site.

OK

How are the standard weekly hours determined for an employee? or are they set to a particular standard? For example, is a person hired to be only a 20-30 hour/week person or is the 20-30 category based on hours worked the previous week or average hours worked over a time period?


We will need to capture the contents of the PTO Chart in the table structure. Since the formatting did not carry over, I'm not really sure on the breakdown. Perhaps you can attach a Word Doc or pdf.

Also, since an employee has both a position as well as a grade that will impact the structure I presented earlier.

Also, since your PTO criteria have differents units of measure (hours and days), we should set up a table to hold those

tblUnitsOfMeasure
-pkUOMID primary key, autonumber
-txtUOM

Now, we will still need the tblPositions I presented earlier but remove the PTO allowance field since the allowance is based on position/grade/hours

tblPositions
-pkPositionID primary key, autonumber
-txtPosition

Now a table to hold the grades

tblGrades (12 records)
-pkGradeID primary key, autonumber
-longGradeNo

Now relate the grades to the positions to which they apply and add the field for the PTO allowance

tblPositionGrades
-pkPositionGradeID primary key, autonumber
-fkPositionID foreign key to tblPositions
-fkGradeID foreign key to tblGrades

Now we'll need a table to define the work hour categories. Technically speaking, since there are 2 bounds/limits (30/40, 20/30, 10/20) for each category, normalization rules would warrant a separate but related table to hold the ends. You may opt to break that rule and put the ends in 2 fields within the same table

tblWorkHourCategory
-pkWorkHrCatID primary key, autonumber
-txtCategory
-LngLowerLimit (Lng=long number integer field)
-LngUpperLimit (Lng=long number integer field)

Or the more normalized approach

tblWorkHourCategory
-pkWorkHrCatID primary key, autonumber
-txtCategory

tblWorkHourCategoryLimits
-pkWorkHrCatLimitsID primary key, autonumber
-fkWorkHrCatID foreign key to tblWorkHourCategory
-txtLimit (Upper or Lower; could use a foreign key to a table that would hold 2 records: upper and lower---your call)
-LngLimit (actual numeric limit; long number integer field)

Now to properly define the PTO criteria, you have to bring together the Position/Grade and Work Hour Categories

tblPositionGradeWorkHourCategory
-pkPosGrWrkHrCatID primary key, autonumber
-fkPositionGradeID foreign key to tblPositionGrade
-fkWorkHrCatID foreign key to tblWorkHourCategory

Now since each PTO has what looks like 3 restrictions: max hours/pay, max hours per year (year worked or calendar year?), max days per year. That describes a one-to-many relationship. I'm not sure what pay means; I assume a pay period (not defined).

First a table to hold the restriction descriptors: Max hours per pay, Max hours per year, max days per year

tblRestrictionDescription
-pkRestrictDescID primary key, autonumber
-txtRestrictDesc

Now a table to define the PTO

tblPositionGradeWorkHourCategoryPTO
-pkPosGrWkHrCatPTOID primary key, autonumber
-fkPosGrWrkHrCatID foreign key to tblPositionGradeWorkHourCategory
-fkRestrictDescID foreign key to tblRestrictionDescription
-spPTO (a single precision number field to hold the numerical PTO value)
-fkUOMID foreign key to tblUnitsOfMeasure

Of course with the new structure, it will impact how we set up the employee/position table

tblEmployeePosition
-pkEmpPositionID primary key autonumber
-fkEmployeeID foreign key to tblEmployees
-fkPositionID foreignk key to tblPositions
-dtePositionEffective


I know we have to relate the employee to the combination of position/grade, but I'm not sure about the work hour category.

What defines a full-time person? a part-time person? Is is based on the work hour category?

[roger556]

Hello,
I have attached the relationship layout as per your previous reply.
Standard weekly Hours are fixed for part time employees they work between 20-30 hour/week.Now there could be instances that they end up working more than those hours but it does not change their status all it does is it accures more PTO time.
Full time employees have to work 40 hrs. I have attached PTO criteria for different employees in a word document.
Maxhours/pay means per biweekly pay period.Also please can you elaborate on how should I go about creating a relationship PTO service,PeriodTime,PTOyears of service table with other tables.

Please let me know if you need any other information.


Thanks
Roger

[jzwp11]

Also please can you elaborate on how should I go about creating a relationship PTO service,PeriodTime,PTOyears of service table with other tables.

Any accrued PTO will be calculated value and thus would not be stored in a table. For example, you would calculate years of service based on some reference date and the date of hire then based on whether the employee is full or part time/hourly category and the number of elapsed pay periods, you would get the factor and use that in the calculation. You would still have to record actual time taken off in order to determine the remaining balance.

A question comes to mind. Why do you have the 30to <40 hour category and the <20 hour category? Do you even use these if all of your people are either full time (40 hrs.) or part time (20-30 hours)? Or do you use the two other categories for part-time employees whose hours do not fall in the 20-30 hours during a pay period? Do full-time employees accrue PTO at a different rate if they do not work the full 40 hours in a week?

[roger556]

Hello,
At this moment we just have those 2 type of employees using either 20-30 hours for part time and for full time it is 40 hours.But I need to make provision that if they do fall below or above those categories than I need to be able to account for the right accural of PTO time.
Please did you get a chance to see my database relationship does it look correct please let me know.By the way I have also created those new tables that you have mentioned in your earlier reply.My dilema is I still have stuff that are not connected but I will get back to it later on, with the size of database we are building here, I need to take it small steps for my learning purpose.I must say I did not aniticipate it to be so complicated but I am glad I am learning from you it is very eye opening.

Thanks,
Roger

[jzwp11]

Sorry, I did mean to get back to your relationship diagram. Everything looks OK so far. I did notice that you had spaces in the field names in the employee table. The general recommendation is to not have spaces or special characters in your table or field names because then you will have to enclose the names within square brackets whenever you reference them. Also, you will want to make sure you do not use reserved words as table or field names. The word year is a reserved word in Access (tblPTOYearsOfService). BTW, you do not need the year field at all. You will calculate the years of service based on whatever reference date you need and the hire date.

You did mention that employees do not transfer between sites, so you can probably just add a foreign key to the tblEmployee to reference the site where that employee works

tblEmployee
-EmployeeID primary key, autonumber
-FirstName
-LastName
-DateHire
-fkSiteID foreign key to tblSites

Also, with the new structure, you have to remove the field LongPositionPTOAccural from tblPositions and the LontTimeStatusAccural field from tblTimeStatus

Continuing from my earlier post, we can now modify the employee position table (tblEmployeePosition) to account for the grade of the employee as well. That table now becomes:

tblEmployeePositionGrade
-pkEmpPositionGradeID primary key autonumber
-fkEmployeeID foreign key to tblEmployees
-pkPositionGradeID foreign key to tblPositionGrades
-dtePositionGradeEffective

Since we changed tblEmployeePositions, that will force us to change tblEmployeePositionRelate as well. I'll leave that to you.

With the structure as it is now, we have taken care of employees, relationships between employees/managers, positions, sites and the factors needed to calculate accrued PTO.

Now we need to take care of actual time employees work and time they take off. So how do you do that now? Do you record day by day, week by week?

I also noticed that in your original design you had a schedule table. Could you please provide a little more detail on that?

[roger556]

Hello,
Employees are required to log in on a website that keep tracks of time.Managers are exempt from it.
Schedule table is to keep track of different time each employees work they have different time schedules including the managers also.Part time employees are suppose to work alternate sundays.
But I think I really do not need to keep track of those specific times etc that is done by HR.
This database is just for managers to keep a track of the PTO time that employees take,the reason for having this database is as employees accure PTO time through each biweekly pay period.It creates confusion as employees have to keep track of how much they have taken etc etc also managers also have to manually keep track of it resulting in lot of errors.
This database is going to try to address that problem it will keep track of how much each employee has taken and also how much they still have etcetc.
So basically each employee at the beginning of the year will start with their allocated PTO time for the year plus 40 hours that they are allowed to carry forward from last year.Than all during the year as they take PTO managers will keep track of it through this database and also if needed print a report for each employee giving them the idea of how much PTO they have taken and how much they still have in balance.
Now every paycheck that the employee recieves it also gives information of how many PTO time that they have taken and how much they have in balance,But this is not accurate so hence we are creating this database to give clear idea to managers and employees as to where they stand with their PTO time.
I have attached a copy of the relationship with updated tables and relationships please let me know if I have to make any changes.
I have a question with regards to different ID's that we have used as foreign keys in various tables now they all need to be exact the same from their main table so they can be linked together am I correct in this understanding?
Tables related to PTO like tblPeriod of time etcetc they will remain seperate from the rest of the tables ?
I have an update I only have to record 3 data one starting balance PTO time which will be default PTO time + PTO time carried forward from last year (i.e: max 40 hrs), secondly PTO time taken during the whole year,thirdly the difference or balance remaining.
Hope this will make things a bit simpler,Please let me know if you have any questions and once again thanks a lot for spending your valuable time helping me really appreciate it.

Thanks,
Roger

[jzwp11]

My apologies, I forgot to integrate the years of service. We will handle it much like we did for the work hour categories. I would start fresh and get rid of the following 3 tables: tblPTOTimeOfService, tblPTOService and tblPeriodTime. You will have to delete the joins before you can delete the table.

Because of my error we will have get rid of tblPosGrWkHrCategoryPTO since the PTO if determined by Position, grade, work hour category and time of service.

So a table to hold the general time of service categories (<1 year, 1-5 years, 6-9 years etc.) just in a text field

tblTimeOfService
-pkTOSID primary key, autonumber
-txtTOS

For each Time of Service category there are 2 limits (upper and lower)

tblTimeOfServiceLimits
-pkTOSLimitID primary key, autonumber
-lngLimitValue
-LimitType (upper or lower) this could also be a foreign key to a table that holds 2 records upper and lower. You could then use this table to link to the above table as well as the tblWorkHourCategoryLimit table. BTW, you have 2 fields in that table: Limit and Limiit. Is one field there to indicate the type (upper or lower) while the other holds the numeric value? You will have to explain.

The way your time of service categories are expressed might cause an issue since the first category (<1 year) is expressed differently that the other categories since the upper limit for the other categories is not a less than value. Expressing things in 2 different ways will cause some issues in calculations.

You may want to consider the following categories:

0 to <1
1 to <6
6 to <10
10 to <25
25 to <999

That way the upper limit is always handled the same way.

Now we have to link the time of service to the position/grade/work hour category

tblPosGrWkHrService
-pkPosGrWkHrServID primary key, autonumber
-fkPosGrWkHrCatID foreign key to tblPosGrWkHrCategory
-fkTOSID foreign key to tblTimeOfService

Now since there are 3 restrictions for each now we have to relink to tblRestrictionDescription

tblPosGrWkHrServicePTO
-pkPosGrWkHrServicePTOID primary key, autonumber
-fkPosGrWkHrServID foreign key to tblPosGrWkHrService
-fkRestrictDescID foreign key to tblRestrictionDescription
-PTOvalue
-fkUOMID foreign key to tblUnitOfMeasure

I have a question with regards to different ID's that we have used as foreign keys in various tables now they all need to be exact the same from their main table so they can be linked together am I correct in this understanding?

The names of the primary/foreign keys do not have to be the same, I maintain similar names other than the pk or fk prefix as an aid in joining the tables. As you noticed, all of primary keys are autonumber. The autonumber data type is a special case of the long number integer datatype. To be able to join two fields in a relationship they must be the same datatype. That is why all of the foreign keys need to be a long number integer datatype.

I have an update I only have to record 3 data one starting balance PTO time which will be default PTO time + PTO time carried forward from last year (i.e: max 40 hrs), secondly PTO time taken during the whole year,thirdly the difference or balance remaining.
Hope this will make things a bit simpler,Please let me know if you have any questions and once again thanks a lot for spending your valuable time helping me really appreciate it.

Since the accrued PTO depends on the actual hours worked in a pay period, how can you not record each employee's hours worked for each pay period? Of course, you will have to record the actual PTO taken by each employee. From there Access can do all of the calculations. There would be no need for you to add records for the carryover amount. Access can do that. The more you allow the database to do, the more accurate it will be.