Subtract Query?

[ckirmser]

If there is such an animal...

My employer uses a third party employee tracking system and I do back end work to massage the raw data for ad hoc reports using tables created by this third party software.

Is there a simple means to have a query compare Table A and Table B where Table A is a list of all jobs in a company - both filled and vacant - and Table B is a list of all employees in the company? The table containing jobs data has no field designating whether a job is filled or not. The only way to determine vacancies is to compare the two tables and select only those job numbers where there is no matching employee.

So, using examples from an earlier question - one that KeithG answered perfectly - let me see if I can demonstrate what I'm trying to do...

The Jobs table has two fields; JobNum and JobDesc.
The Employee table has three fields; EmpNum, EmpName, JobNum.

Here's Table A, Jobs;

100, Boss
105, Aide
200, Manager
201, Worker
202, Worker

Here's Table B, Employees;

111, Montgomery Burns, 100
112, Waylon Smithers, 105
121, Homer Simpson, 201
122, Lenny Leonard, 202

Jobnum is the link between the two tables in the query and the query's fields are;

JobNum and JobDesc

The result I need is;

200, Manager

The only vacant job.

So, how do I do a subtract query, if that's even a proper term? How do I have a result that is all of Table A that does not have a match in Table B?

Thanx!

 

[Rabbie]

Use the "Find unmatched Query Wizard". It does what you want. You will find it by clicking on the New button on the queries pages

 

[ckirmser]

Thanx, Rabbie!

Now that I see what the wizard creates, I see the process to do the job.

I thought it was something simple, but my brain wasn't wrapping properly...

Mondays...