Need to pull all records in one query if a value exists?

[rreiling007]

Hi Access Experts!

I have a question that might be fairly easy for you.

I have one table let's call is table1. It contains about 5 columns with standard employee data with about 10,000 rows of data containing about 1,150 employees. All employees have multiple rows of data. There is one column titled JOB. I need to pull all rows of data for each employee ONLY if there is at the minimum one value for the employee in the JOB column. I do not want to pull employees that have no values in the JOB column. They can be excluded from the query.

Example of all record for one employee. I need to pull all rows of data ONLY if the employee has a value in the JOB column.

Name EE_ID JOB CAT EFF_DATE

John Doe 1006 CLERK F 01-JAN-2010
John Doe 1006 P 21-JAN-2010
John Doe 1006 CLERK P 01-FEB-2011
John Doe 1006 F 01-MAR-2011
John Doe 1006 P 01-APR-2011
John Doe 1006 CLERK F 01-JUL-2012

Appreciate any guidance you can provide.

Thank you!

 

[JHB]

You need 2 queries.
The first one to pick out all the employees who has a job.

SELECT DISTINCT Table1.Name, Table1.EE_ID
FROM Table1
WHERE Table1.JOB Is Not Null;

The second one to pull all rows of data for the employee who was pick out in the first query.

SELECT Table1.*
FROM Query1 INNER JOIN Table1 ON Query1.EE_ID = Table1.EE_ID;

Only for info - "Name" is a reserved word in MS-Access, call the field EName instead.

 

[rreiling007]

Thank you! This resolved my issue.

Appreciate it!

 

[JHB]

You're welcome, luck with your project.