×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

Hire database freezing old Data in table

Hire database freezing old Data in table

Hire database freezing old Data in table

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

RE: Hire database freezing old Data in table

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
 
 
 

RE: Hire database freezing old Data in table

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

RE: Hire database freezing old Data in table

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
 
 
 

RE: Hire database freezing old Data in table

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

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