Hire database freezing old Data in table 1

[james33]

I am in the process of setting up a Hire database,
Table1: Customers
Table2: HireMachines which looks up values in
Table3: MachineType
Table4: HireJobs which looksup Table1,Table2
I managed to use a qry on all 4 tables to include all the relevant data for my 4 forms:
frmJobsOut,
frmJobsReturned,
frmJobsUnpaidHistory and
frmJobsHistory.
Based on the values of fields [jobStatus],[Invoice/Receipt], [PaidUnpaid].
What I'd like to know is, what to do with old records when rates of hire change? I remember something about a Rates Table, but I'm talking about 80 different Machines here. Though I could calculate from a base rate/hr to 1/2 Day, 1 Day, Week, and Month. It has 'gotten' my grey matter churning,
Any help would be appreciated.
Regards
James Maidment

 

[kathryn]

James,

If I understand you correctly, your problem is similiar to a retail store, which needs to store invoice data, showing what was paid on for a pair of jeans on January 1, even if the price rose on January 15th.

The answer is that the invoice data must be written to a table. So in your case, you are going to have to store the necessary data in a History table.

I was confused by your use of the term Hire, but then I realized that we might be having a British/American English problem. I think you have a database where customers can rent equipment?

If so, you can have a rate table, which might look like this:

MachineID (PK)
Rate

or, if you want to keep historical rate info,

MachineID (PK)
RateEff (PK)
Rate

Even if you use the second choice, I would still recommend storing the rates at the time of hire in a separate table, because the queries to recreate the charges can get a bit hairy.

Hope that helps.

Kathryn

 

[james33]

Thank you for your suggestions Kathryn, Though I have a bit of a problem understanding the bit:
'MachineID (PK)
RateEff (PK)
Rate'
Is it possible that I could use a select query to obtain the customers/machines/machineType and HireRates from the different tables and then append the Data to a History Table at the time of calculating it or from a control on the form.
I know it is considered 'Bad practice' and would mean storing but this table would only be used as a record of transactions and for informing Account Customers of their unpaid Hire Jobs, oh and probably a few other things in the process (the mind races with ideas).
I would appreciate any suggestions as I am only a beginner in this Game!
Regards
James Maidment

 

[kathryn]

James,

Actually, what you suggest is the way I would go. The only reason I suggested the format of
MachineID (PK)
RateEff (PK)
Rate

was if you needed to keep a history of rates. The data, in that case would look like:

MachineID RateEff Rate
1 6/30/2000 $40.00
1 1/1/2000 $60.00
etc.....

Don't worry about storing the results of a calculation. This is generally considered bad form, with the exception of your situation, when the calculations will not be able to be reproduced in the future, due to changes in some of the inputs.

So, what exactly do you need help with?


Kathryn

 

[james33]

Thanks I'd only just gone away and come back.
Well, a couple of things spring to mind
1. because I use Forms based on select queries to show jobs that are 'Out' or 'In' or 'History' I have a problem when I want to both delete a job and change the machine status back to in. I have to provide a get out for staff who only realise they did it wrong or the machine is unavailable after they have committed the job to print. I must always (obviously) have the machine status correct but it has to be reset with the delete of the job.
and at that point the Undo function won't work and shows an error (unavailable at this.....etc). On that point can I customise the error messages in Access that tell you, you have made a Boo Boo
Thanks for your assistance so far.
Jim