Build a query to calculate differences

[Jon von der Heyden]

Hi,

I have 2 tables - Purchases and Sales. Both have a common Item_ID (which represtents the item type bought or sold) and QTY (quantity bought or sold). I would like to build a query that will detail for each item, the difference between the qty in purchases from the qty in sales.

I'm stuck, please help.

Thanks,
Jon

 

[Andrew Fergus]

Hi Jon

For the purposes of this example I will assume the two tables are named "purchases" and "sales" and they both have a field titled "qty". I will also assume that you will want to know the purchased quantity less the sales quantity. I'm not sure if you can have a qty in the sales table without a qty for the same item in the purchases table, but for the purposes of this example I will assume you can't.

Create a new query, add sales table, add purchases table, Ok.

Link the two tables based on the "item_id" field. Double click the link and change it to "Include all records from purchases and only those records from sales where the joined fields are equal", Ok.

Field 1 on the query = "item_id" from the purchases table
Field 2 = "qty" from the purchases table
Field 3 = IIf(IsNull([sales].[qty]), 0, [sales].[qty])
Field 4 = [purchases].[qty] - IIf(IsNull([sales].[qty]), 0, [sales].[qty])

Save and Run.

You can add any other fields you want onto the query and if the formula is the wrong way around (i.e. you want sales less purchases) then you can use the same logic but link the tables the other way around and use the "isnull" part of the formula on the purchases qty instead of the sales qty. Given you have used the same variable name "qty" in both tables, then you must prefix the variable name with the table name for any math functions in the query.

HTH, Andrew.

 

[Jon von der Heyden]

Perfect, exactly what I'm looking for. Thanks very much Andrew.