Creating a calculated link between two tables RRS feed

  • 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 #

    Tuesday, October 9, 2018 7:55 PM

All replies

  • 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)

    Tuesday, October 9, 2018 8:51 PM