Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating cost rates (2000)

    Hi,
    I have a current database where I have employees that are billable. I have an employee table that has their billable rate. As of April 2004 the rate has increased and I'm at a block on how to update it. If I just type in the new rates, the database will use the new rates for the old data and I don't want that. Any thoughts?
    Thanks,
    Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating cost rates (2000)

    I hope you have a date field you can use to distinguish old data from new data.

    Since your rates will probably change again in the future, you should have a separate rates table tblRates, with three fields:

    EmployeeID (or something like that) - link to the employees table.
    ValidFrom - Date/Time field that specifies from which date a rate is valid.
    Rate - Currency field that specifies the rate.

    The EmployeeID and ValidFrom fields together form the composite primary key. This table is related to the employees table by EmployeeID. This setup will allow you to have an unlimited number of rates for an employee, each with its distinct ValidFrom date. You can use queries to calculate the rate for a specific employee for a specific date.

    I have attached a similar example demonstrating how to do this (it uses products and prices instead of employees and rates, but the idea is the same.)
    Attached Files Attached Files

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Updating cost rates (2000)

    Another thing you might do (in addition to Hans' suggestion, or as an alternative) is to have an JobBillingRate field in the table that holds the Billing Record line items. This value would be filled in when the record is created by looking up the appropriate rate for that employee at that time.

    This also gives you the flexibility to vary the rate for a specific job if you wish, without changing the value for the employee.
    Regards
    John



  4. #4
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating cost rates (2000)

    Thanks, that's what I figured I'd have to do, just wanted to make sure I wasn't missing something easier. Thanks! Deb

Posting Permissions

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