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

"Michelle" wrote

> 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!!

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