Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009

    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
    Salary: $60'000
    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)
    Effort: 50%
    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?

  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    ^^ You call this "finishing touches" ??

    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Feb 2009
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts