Link to home
Start Free TrialLog in
Avatar of Lawrence Salvucci
Lawrence SalvucciFlag for United States of America

asked on

Accrual in Access Query

I need to set up a personal/sick time accrual query to show the total hours accrued by employee. Here is the breakdown of our accrual system:

You are not entitled to start using your sick time until 90 days after date of hire. But you will start accruing from your hire date.
You will accrue 1 hour of sick time for every 30 hours worked to a maximum of 40 hours in a calendar year.

Any ideas on how I can create this query to calculate the sick time? I will have the employees total hours worked stored in a table so I can use that to find out how many hours they have worked towards accruing hours.
Avatar of Christopher Jay Wolff
Christopher Jay Wolff
Flag of United States of America image

I don't have access to Access.  :-)

Not much going on here so I put this into Excel for you to look at if you like.  Workbook attached.  If it has to be Access then so be it.  The thought with this was Query Access to get the columns of employee data to make a table.  Read the data into sheet 2 'Employee Data Table' and adjust your formulas in sheet one according to how your actual Employee Data Table appears in sheet 2.

It's a little sloppy and there are things to work out like compensating for the calendar year and so forth, but you get the idea.  Rather than finish it all, I thought I'd see what you think.  You just enter the employee's SSN and the date of your query in C1 and C2 (I think it was) and it appears.

What do you think?  Not for your application?  Oh well.  I stayed busy for a while.

Have fun.
EESickTime.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Christopher Jay Wolff
Christopher Jay Wolff
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Lawrence Salvucci

ASKER

OK I need this in MS Access, not Excel.
Just hung up with EE support.  I cannot upload to this question without 404 error when opening my uploads.  They confirm they also cannot open my uploads on this question and get the 404 error.  I emailed the workbook to EE and they can open it in Excel fine there locally.  They are looking into it and will get back to me via email.  Maybe they will post here also.

To see what is wrong, could you please confirm you have the same problem and also get the 404 error when opening my uploads?


Sorry for spending your time on Excel.  If EE can get the Pivot attached to this question thread, you may want to see the Pivot to know it's there however.  Especially with the direct connection to Access.  I'll hang back and watch what an Access person suggests.
SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
FWIW
I work for a company that has about 1300 employees and accruals are still a *nightmare*
Split shifts
Partial weeks
OT (over 8hrs or over 40 hrs)
Various accrual schedules,
Leap years
Bi-monthly payroll
Terminated employee accruals
Accruals during Vacation/Sick
FML
...etc
Hi Jeff...I totally understand where you are coming from. This isn't my first rodeo with accruals but my first in Access. We have a home grown HRIS system that is built in Access so we'd like to keep things as is with our system instead of buying an out-of-the-box system. But the powers that be would like this system built in our HRIS DB that we are currently using.
Ok,
Then can you post a sample DB with some sample data?
...and then post an example of the "exact" output you desire.

For example:
Based on the sample data
"I want Bill to show an accrual of____"
"I want Sally's accrual to be ______"
"Fran should have an accrual of: ____"
...etc
EE fixed the link to EESickTimeWithPivot.xlsx above.  That workbook includes the previous.