Display items with they appear in different sections of a table

[bzeyger]

I have an Access VBA Form that uses information from various tables.

The form I have used uses a subform.

I am trying to set up a query so that it displays what work an employee has
done. The user would select an employee from a list. That selected Employee
would be listed in the main form.

Example:

EMPLOYE: joe smith


Job 1
Job2
Job3


Joe Smith was assigned as employee1 on one job, employee2 on another job,
and employee3 on the other.

The list should display all occurances.


The Query returns a value from the table form the employee 1 field. However,
a job can have up to 3 employees (employee 1, employee 2, employee 3).

How can I set up the query or parent child relationship to display the jobs
of the employee selected.

Right now, it only displays the information for the employee if he is
employee 1.

How do I get it to display the employee if he is employee 1, employee 2, or
employee 3? I not sure if I have the relationships set up correctly.

 

[KARL DEWEY]

Your Job-Employee table needs to have a record for every employee and job
combonation like this --
Job Emp
1 1
1 2
1 3
2 2
2 4
3 1
3 2
This is a one-to-many relationship from Job to Employee.

 

[Beetle]

How do I get it to display the employee if he is employee 1, employee 2, or

employee 3? I not sure if I have the relationships set up correctly.

The fact that you have fields like Employee 1, Employee 2, Employee 3 in
your jobs table is a good indication that your tables and the associated
relationships are setup wrong. You essentially have a many-to-many
relationship between jobs and employees so you need another table
to handle the relationship. Something like;

tblJobs
*****
JobID (Primary Key)
JobName
JobDescription
Etc.

tblEmployees
**********
EmployeeID (PK)
LastName
FirstName
etc.

tblJobEmployees
************
JobID (Foreign Key to tblJobs)
EmployeeID (FK to tblEmployees)

The Primary Key for tblJobEmployees would be a combination of JobID
an EmployeeID. With this setup you could assign as few or as many
employees to a job as you like.

Your would then query tblJobEmployees to get the info you want.