Subtracting tables?

[mgp]

I've used SQL some and DBXL (DBase clone) and Paradox lots, learning Access2000 as I go here...

I'm used to being able to subtract one table from another, leaving the unmatched results in another table. For instance, Table1 has 6 employees and Table2 has 3 of those employees: subtracting Table2 from Table1 leaves a new table with the 3 'leftover' employees who werein Table1 but not in Table2.

How the &$!%$ do I accomplish this in Access? I've been stuck right here for 3 days, now.

This should be simple, and I'm trying to accomplish it as an event so the user can find the 3 'unused' employees upon a click. It may be some filter or query, but how is this accomplished with whole tables?

Sure would appreciate any input.

 

[GumbyD]

You need to use an outer join from the larger table to the smaller and then use is null criteria on the key field in the smaller table. So for example in table 1 you have 10 employees and in table 2 you have 5 employees to get the 5 employees in table 1 that are not in table two you would create an outer join from table one to table two on the EmployeeID (or the key field) and then in the grid pull the EmployeeID from both tables. In the criteria for the smaller table EmployeeID field put 'Is Null'. The results of this query will give you the unused/unmatched records from table 1. If you then need these results in a table you can change the query to a make table query and move the data into a new table.

GumbyD

 

[mgp]

Thanks, Gumby:

I'll give that a try.

 

[mgp]

Many thanks to both of you: the 'Unmatched Wizard' thing pointed out that the ADODB engine doesn't like the OUTER JOIN, but it would accept the LEFT JOIN, which worked.