Maintaining Historical Data in a Field
Maintaining Historical Data in a Field
(OP)
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
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
Talk To Other Members
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()).
MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over
RE: Maintaining Historical Data in a Field