Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Maintaining Historical Data in a Field

Maintaining Historical Data in a Field

Maintaining Historical Data in a Field

I have created a database for someone to manage an ongoing project that requires weekly invoicing to a client and payment to numerous subcontractors. I have three tables - a "master" table which contains the date (of books) received and invoice date, a "detail" table which contains the title and page count of each book as well as typist and client billing information, and a typist rate table which contains the typists' names and base page rates.

When she enters a book and associates it with a typist via a combo box, the typist page rate is automatically filled in and the total payment calculated based on the page rate and page count fields.

Occasionally, a typist may get a raise in her base page rate effective from that date forward. The way the database is currently structured, if there is historical data entered based on a page rate of, say, $1.50/page, and she gives that typist an increase to, say, $2/page, all of the historical data is automatically updated to $2/page.

How can I set up the tables and/or relationships (or whatever) to allow for increasing the base rate without changing the historical data?

Thanks in advance for any help!


RE: Maintaining Historical Data in a Field


You need to set up another table with the typist name, rate, and effective date.  Use the new table to determine the invoice amount.  Items billed before the effdate of a rate are biled at the "old" rate, while newer items are billed at the newer rate.  You may also need to add a "submission date" and/or page count field(s) to the typist work and use the submission date/page count to get accurate invoices.

Also, you may - at some point - need to accomodate more than two pay rates for typists, so the general soloution needs to really correlate the dates of work and pay rate in a more or less general manner.  This USUALLY includes some default dates for start and end of a pay rate (default start could be earliest work submited date, and default end could be now()).

There is never time to do it right but there is always time to do it over

RE: Maintaining Historical Data in a Field

Thanks so much for the speedy reply, Michael. I will experiment with that solution later today. I hadn't thought of qualifying the rate based on a date range - I had been thinking I would need to create a new table that held the complete information for each book entry, which would, of course, create a lot of redundancy and get bloated over time. Your solution sounds leaner and more efficient than where I was going. Thanks again! -- Carenne

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close