PTO Balances and Accruals

[breakingme10]

I think I may be in over my head. I've started a database to monitor PTO hours. This was requested by a supervisor. So I have the database just monitoring how many hours of PTO any given employee has taken based on what's been entered in the PTO request form. Now she wants to give me beginning PTO balances to work off of. So I need a way to add in the accrued PTO hours each monday to those beginning balances and subtract what PTO has been requested. I've attached my database. Can someone help me on what to do from here?

[orange]

PTO? We need some business context. How about describing your requirement in plain English --just as you would tell an 8 year old?

Acronym Finder Rank Abbr.

	Meaning
PTO	Parent-Teacher Organization (variant of Parent-Teacher Association)
PTO	Power Take-Off
PTO	Paid Time Off
PTO	Pátio (Portuguese postal usage)
PTO	Patent & Trademark Office
PTO	Personal Time Off
PTO	Phantom of the Opera

...and there are more.

The participants of this forum, and many others, are unpaid volunteers who have an interest in database/Access etc and are trying to assist others with their experience and advice. Participants come from various backgrounds and not all understand ( or can correctly guess) every acronym or misspelling we encounter. So help us help you and give us some context and possibly a few examples to help readers familiarize with your request.

Good luck.

[AccessPower]

I would have your queries update your EmployeeData table.

Add PTO Remaining to the EmployeeData table. Have the PTO requests subtract from what's available (making sure there is available hours first).

You can also two fields
"LastUpdated" - This will contain the date of the last time the accrued dates were updated.
"NumberOfWeeksSinceLastUpdate" - This will find the number of weeks between "LastUpdated" and when the query is run.

Set your database to run a query that will add accrued time to "PTOAllowed" when the user logs in if "NumberOfWeeksSinceLastUpdate" is greater than or equal to 1. This will update each user based on their accrual rate as you already have that information within the database.

[vicatucd]

If I were you, I would create another table that consist of PTO accruals. It should have employee ID, PTOdate, and number of hours accrued. To find out the balances, I would create a select query with a calculated field. For instance, I would create the select field to contain the employees name, [number of hours accrued], [number of hours requested], [Balance] ... balance would be easily calculated using the following formula Balance: [number of hours accrued]-[number of hours requested]

[breakingme10]

i forgot to add that PTO (Paid Time Off) is reset at the anniversary of their hire date. Each year it resets to 0 unless they have some rollover. they are allowed 16 hours to rollover into the new year.

[breakingme10]

i was just told that my calculations for the weekly accrual basis were unnessary. We get 56 hours on our 1 year anniversary and after 3 years we start getting 96. and each year it just resets to 56 and 96 unless they have the 16 hours of rollover that they can carry over into the next year.