I have a tables called InventoryReceived, InventoryAllocated, and InventoryTransactions. I am having trouble creating a form that will query both these tables and report back totals into the correct fields in the form.
In the InventoryReceived table there is a field for TransactionID, TransactionDate, QtyReceived, OnHandLocation, PartName, EnteredBy, and TransactionDescription.
In the InventoryAllocated there is a field for TransactionID, TransactionDate, QtyAllocated, LocationAllocatedFrom, PartName EnteredBy, and TransactionDescription.
In the InventoryTransactions table, there is a field for TransactionID, PartName, OnHandLocation, UnitsInStock, UnitsAllocated, AvailableUnits, UnitsOnOrder, TransactionDate, and EnteredBy.
I have the following Forms:
Allocated Inventory with fields to enter the information for equipment allocated out (EnteredBy, TransactionDate, PartName, QtyAllocated, LocationAllocatedFrom, TransactionDescription) This form places information into the InventoryAllocated table.
Inventory Received with fields to enter the information for equipment received by location (EnteredBy, TransactionDate, PartName, QtyReceived, OnHandLocation, TransactionDescription).
InventoryTotals with fields OnHandLocation, PartName, UnitsInStock, UnitsAllocated, Available Units, UnitsOnOrder).
What I am trying to do is to have the InventoryTotals form populate itself with the Units in stock total (sum received of the part name), units allocated total (sum allocated of the part name), the available units total (units in stock minus units allocated), and the units on order (sum on order of the part name). The control would need to be the On Hand Location.
Example: The InventoryTotals form is opened. The user enters the On Hand Location they would like to see the inventory totals for into the On Hand Location box and presses enter. The form automatically brings back the totals for each part name for that location.