Calculating values from two tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables Equipment and EquipmentRoom.

Table 1 - EquipmentRoom
Fields - Equipment Number, Qty on FP

Table 2 - Equipment
Fields - Equipment Number, Current Room Qty, Total Qty, Discrepancy

First - Calcuate the Qty on FP for each type of Equipment Number
Second - Have the Equipment Table Use this Sum for the Current Room Qty field
Third - Substract the Curren Room Qty field from the Total Qty field to be
updated in the Discrepancy field.

I thank you for your help!!

Michelle
 
I have two tables Equipment and EquipmentRoom.

Table 1 - EquipmentRoom
Fields - Equipment Number, Qty on FP

Table 2 - Equipment
Fields - Equipment Number, Current
Room Qty, Total Qty, Discrepancy
Click to expand...
First - Calcuate the Qty on FP for each
type of Equipment Number
Second - Have the Equipment Table Use
this Sum for the Current Room Qty field
Third - Substract the Curren Room
Qty field from the Total Qty field to be
updated in the Discrepancy field.
Click to expand...
I thank you for your help!!
Click to expand...

Maybe you should hold your thanks until you see the answer.

You need to rethink your table design. It is not good practice to keep
summations, particularly chains of summations, for information that you can,
and should, calculate just when you need it. It is one of the basics of
relational database design, but is also a good practice no matter how an
application's data is stored.

That will require some Table redesign, and creation of some Totals Queries.
I know that may be more immediate work, but it will pay off in the long run.

Larry Linson
Microsoft Access MVP
 
Back
Top