Unanswered: Please help with date calculations & variables in queries!
I'm putting the finishing touches on an Access database for work. This is my first experience with Access, so I've been mostly learning on my own, but I'm struggling with the final component.
My work has ~15 employees, all of whom are paid from various projects. I need to track the employee's wages (either salaried or hourly) as they get raises, multiply the wages by benefits rates, sum the totals, and finally generate a report stating the project balance (Project Sum - Summed employee wages). This would be easy, but it gets complicated. It's easiest to use an example of what I need.
Employee: Doe, John
Pay Period: July I (~July 1-15th)
Fiscal Year: 2009
Monthly Salary: $5'000 (I generate this in a query so that I can calculate the employee's effort - In other words, an employee might get 50% of their salary from one project & 50% from another project)
Fringe Rate: 30%
F&A Rate: 15%
Project: Acme 350
So: John Doe would earn Effort * Salary ($2500 * 50% = $1250) for the two-week pay period of July I. Multiplied by the Fringe Rates & F&A Rates, this comes to ~$2437. This total would be deducted from the budget of Acme 350.
If employee's salaries were constant across fiscal years, this would be easy. Unfortunately, salaried employees get raises in the middle of the fiscal year (October I, to be exact) & hourly employees can get a raise at any time. I need to track salary histories in order to keep the budget accurate. For instance, if John Doe gets a raise to $70'000, I need past pay periods to retain the $60'000 figure.
I can't figure out how to do this without using dates (e.g., June 1-15 rather than June I). However, my boss has told me that I have to use these types of pay periods. I apologize for the long post, but does anyone have suggestions?
I know. If I didn't feel hopelessly confused, I wouldn't be asking. While it would be great if someone were actually able to help, I know it's a big task. I'm really unsure of how to convert the pay periods JUL I, AUG I, etc. into dates that I can work with. I'm guessing that I'll need to create variables of some sort, but I'm having difficulty wrapping my head around it.