how do i get my query to do this

[Guest]

Alot of times I have to work with large groups and need to whittle that group
down to just the ones that do not match.

For example I have two tables that should have matching data but is missing
some like this: in the example each table has 2 fields, item code and # units

table one

item code 1 55 units
item code 2 50 units
item code 3 20 units
item code 4 10 units

table two
item code 1 50 units
item code 2 40 units
item code 4 5 units

Now say I want a query to run and subtract #units in table 1 from # units in
table 2 for the same item codes I should get this

item code 1 5 units
item code 2 10 units
item code 3 20 units
item code 4 5 units

What i get is the query ignores those items that are not found in both
tables so my return only shows the remaining units for items 1, 2, and 4.

How do I get it to include item 3 in this example?

Thanks

 

[Marshall Barton]

Alot of times I have to work with large groups and need to whittle that group
down to just the ones that do not match.

For example I have two tables that should have matching data but is missing
some like this: in the example each table has 2 fields, item code and # units

table one

item code 1 55 units
item code 2 50 units
item code 3 20 units
item code 4 10 units

table two
item code 1 50 units
item code 2 40 units
item code 4 5 units

Now say I want a query to run and subtract #units in table 1 from # units in
table 2 for the same item codes I should get this

item code 1 5 units
item code 2 10 units
item code 3 20 units
item code 4 5 units

What i get is the query ignores those items that are not found in both
tables so my return only shows the remaining units for items 1, 2, and 4.

Use a Left Join instead of an Inner Join. Be sure to use
the Nz function (in the subtraction) for the field in table
two.

If you have another question about a query, please post a
Copy/Paste of the query's SQL view.