Calculations in Tables

[liljohn]

Hello there.
I am just wondering if i could do calculations in tables.
I have a table named WORKERS, and they are paid by the miles they drive for a particular job. The MileRate is under the table of WORKERS.
However, the miles covered are in JOB DETAILS.
Is there any way to automatically calculate the WORKERS salaries through JOB DETAIL table?
For instance i under the field SALARIES in WORKERS's table to multiply their milerate eg. 0.4 with the MILES_COVERED in JOB DETAILS table.
Thanks in advance!!!

Any help will be greatly appriciated.

 

[Rabbie]

Its not usually a good idea to store calculated values in tables. Just calculate the values when you need them in a query.

 

[WayPay]

I am just wondering if i could do calculations in tables.

No, not in tables. You can do that in queries.

.. under the field SALARIES in WORKERS's table ..

Your WORKERS table should probably not have a SALARIES field. Read up on Database Normalization.

Having said that, a query like

SELECT Workers.Name,
    Jobs.JobID,
    Workers.MileRate * SUM( [Job Details].NumberOfMiles ) AS [Job Salary]
FROM Workers
    INNER JOIN (Jobs
        INNER JOIN [Job Details]
        ON  Jobs.JobID   = [Job Details].JobID)
    ON  Workers.WorkerID = Jobs.WorkerID

should give you the total salary per job for all workers (assuming there's only one worker per job)

 

[liljohn]

Thank you very much guys.

The thing is that i was hoping i could do it in tables as well.

Thank you once again for your time!

I will do it in queries just to avoid the hassle.

 

[WayPay]

Hey, you can always make an update query out of that if you still want to keep the totals stored. Thing is, at some point you're going to look at a wrong total if you forgot to update it. Murphy's Law dictates that the boss is going to forget to do it come next payday .