Using a query to sum and subtract differences

[sljensen]

I want to design a query that will be use two different tables with the same criteria (the lot number) to filter for the corresponding weight. In one table this lot number is specific to only one record whereas in the other table there could be multiple records with the same lot number. Ill also need to sum the multiple records into a total and then take the difference between the total weight (from the table with only one record) and the summed weight of the multiple records from the other table. Then this value I'd like to be returned to the form with the combo box. How do I do this?

 

[Sketchin]

1) Create a query and add the 2 tables

2) Create an expression to sum records:
SumOfRecords: Sum([YourFieldName])

3) To find the difference, create an expression that looks something like:

Difference: [TotalweightTable1] - (Sum([YourFieldName]))

4) If you want to only select specific records, you will have to set a criteria like:

[Forms]![YourFormName]![IDofYourField]

5) On your form, set your control source of a textbox to be "Difference" or "SumOfRecords"