Creating a calculated link between two tables

Question

I am working on building an inventory database for a project I am working on. I have two tables Inventory and Checkout. 

The columns in Inventory are | Tracking # | Description | Location | Total QTY | Checked Out|

The columns in Checkout are | Tracking # | Checkout Date | Work Order # | Quantity|

I need to know how to set up the "Checked Out" column in Inventory to be equal to the sum of all "Quantity" from Checkout that share the same Tracking #

Answer 1

I wouldn't include a Checked Out field in the Inventory table, but calculate it in a query.

For example, create a query based on Inventory, and add a column

Checked Out: DSum("Quantity","Checkout","[Tracking #]=" & [Tracking #])

This assumes that Tracking # is a number field. If it is a text field, use the following:

Checked Out: DSum("Quantity","Checkout","[Tracking #]='" & [Tracking #] & "'")

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)