Join

[Guest]

I'm not sure if what I need can be done using a join, but here it goes.

I have a table that has job code functions in it. Here is the basic table
structure:

Table1
EmpNo
JobCode1
JobCode2
JobCode3

I have another table that has a list of job codes that I want to query on:
Table2
1 (jobcode)
3
4
5
10

I want to pull all records from table1 where any of the jobcode fields have
a value that is in Table2.

How would I do this?

Thanks.
J

 

[Guest]

In both of your examples, are they column headings that go across or data
going down? If column headings, you need to normalize your tables. It should
look more like:

Table tblEmpJobs
EmpNo JobCode
123 1
123 2
456 3
456 2

Table tblJobCode
JobCode JobCodeDescription
1 Roofer
2 Drywall
3 Plumbing

SELECT tblEmpJobs.*, tblJobCode.JobCodeDescription
FROM tblEmpJobs, tblJobCode
WHERE tblEmpJobs.JobCode = JobCode.tblJobCode ;

You also need a table tblEmployees as I bet that an Employee can have more
than one JobCode and most JobCodes have more that one Employee.

 

[Guest]

Sorry - I should have been a little clearer.

Table1
EmpNo JobCode1 JobCode2 JobCode3
1 1 2 3
2 2
3 2 3 4
4 10

Table2
JobCode
1
3
4
5
10

Unfortunately, I am stuck with the table structure in Table1. I was using
Table2 to store the jobcodes I want to include in this analysis (rather then
entering them in a query criteria). There is a table with employee codes and
info and table with job codes and info. There also isn't any hierarchy as
far as 'primary job function' being in jobcode1 field. In the example, I'm
trying to find the best way to pull all employees who have a job function of
1, 3, 4, 5 or 10. However I simplified the example, in my case there are
about 30,000 employees and about 20 job codes that I want to query on.

J