Losing Records in a Query

[ckirmser]

Is it possible to have a query retrieve all information from one table, but only partial information from another?

For example, suppose I have two tables, Jobs and Employees.

The Jobs table is a list of all available jobs in the company; some filled, some not. Each job is assigned a Job Number which is unique.

The Employee table is a list of all employees in that company. Each employee is assigned to a particular job, referenced by the Job Number.

The query's job is to provide a list of all Jobs, but with employee information added to those jobs that are filled.

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

Jobs has five records;

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

Employee has four records;

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 its fields are;

JobNum, JobDesc, EmpNum, EmpName

What I want is;

100, Boss, 111, Montgomery Burns
105, Aide, 112, Waylon Smithers
200, Manager
201, Worker, 121, Homer Simpson
202, Worker, 122, Lenny Leonard

But, what I get is;

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

Job Number 200, the Manager slot, is being dropped out.

This is the SQL code for the query;

SELECT Jobs.JobNum, Jobs.JobDesc, Employee.EmpNum, Employee.EmpName
FROM Jobs INNER JOIN Employee ON Jobs.JobNum = Employee.JobNum;

How can I get the complete Job list with employee data added to those jobs which are filled, while leaving the vacant positions - well, vacant, but still included in the query's result?

Thanx!

 

[Banana]

Use the query you just built as a basis for a second query. In that second query, you need the table of Jobs. Pull all listing of jobs from the table of Jobs and put the person's name next to the job from the first query. Be sure to use Nz() function. (Search forums if you need specifics on how to use Nz() )

This should give you the complete listing of jobs, including those that are vacant. HTH.

 

[KeithG]

try the below SQL

SELECT Jobs.JobNum, Jobs.JobDesc, Employee.EmpNum, Employee.EmpName
FROM Jobs Left OUTER JOIN Employee ON Jobs.JobNum = Employee.JobNum;

 

[neileg]

I agree with KeithG. Note it is the left join that is important (I think Access calls this a type 2 join). When you join tables in a query, Access defaults to an inner join (type 1 in Access speak).

 

[ckirmser]

Thanx, KeithG! That fit the bill perfectly.

I've been dumping both tables into Excel and massaging the data with VLOOKUP to get the final output and decided that if Excel could do it, then Access could; I just had to find the way.

This was the way.

And, I didn't need Yellowbook.com...