Maintaining Historical Data in a Field

[Carenne]

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!

Carenne [sig][/sig]

 

[MichaelRed]

Carenne,

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()).

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[Carenne]

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 [sig][/sig]