Query

[look4help]

Wondering if someone could assist a Access beginner. I have set up a data
base with the three tables below. The second table holds more that one entry
for each of the employees in the Master table and is joined through
EmployeeNumber. I am trying to set up a query that selects the employee’s
name from the master table and only one of the records related to that
employee base on criteria that I specify. This criteria could be one or more
of the columns in the second table. As an example I may require that all
jobs with an effective date some time in the future be listed. I have
created a third table defining the job since more that one employee can be
assigned to the same job. This info would also be included in the query. I
have tried embedding one select statement within another but to date have had
no success. I usually get only one record or multiple records from table two
for each employee. Any assistance would be appreciated.

Master Table
tblEmployeeInfo with Columns

EmployeeNumber (Primary Key)
EmployeeName
EmployeeDateOf Hire
EmployeeSeniorityNumber

Second Table
tblJobAssignment with Columns

EmployeeNumber
JobIDNumber (Primary Key)
JobPostNumber
JobEffectiveDate
JobCompleteDate
JobDetail
Comments

Third Table
tblJobInfo with Columns

JobIDNumber
JobLocation
JobEquiipment
JobPosition

Thanks

PS Any suggestions on some reading for advanced SQL statements.