This is driving me crazy

[Guest]

The more I work with this program the more I realize how little I actually
know.

Some please give me a clear answer. I have two tables. One with Employees
and one with jobsites. Several of our employees work on more than one
project at a time. I am trying to generate a report that will list all of
the employees working on a project (when the user enters the appropriate
project #). The problem I have is that I have not been able to build my
expression in a way that will work. I either end up with one name because
only one person on that project is working on 2 projects, or I end up with
none.

My employee table has the fields current and 2nd, while the jobsite table
has the field project #, this project number field is the common factor, both
the current and 2nd fields on the employee table use the project #s listed on
the jobsite table. So how do I write this expression that will tell the
query to look into the current field and then look into the 2nd field without
excluding everyone not listed in both fields?

 

[Guest]

You need to change your database structure to the following.
Employee --
EmpID - Autonumber - Primary key
EmployeeNumber - text
LName - text
FName - text
MI - text
DOB - DateTime
etc.

Jobsite --
JobID - Autonumber - Primary key
ProjectNum - text
ProjName - text
StartDate - DateTime
EndDate - DateTime
Active - Yes/No
etc

Then have junction table so you can have a one-to-many relation between
employee & jobs and a one-to-many relation between jobs & employees.

Emp_Job --
EmpID - number - integer - foreign key
JobID -number - integer - foreign key
StartDate - DateTime
EndDate - DateTime
Active - Yes/No
etc

Set the relationships between the tables. Build a query with left join from
employee to Emp_Job and left join from Jobsite to Emp_Job.

Use this query with a form/subform (employee to jobs) setting the
Master/Child links on EmpID.

Use this query with a form/subform (jobs to employee) setting the
Master/Child links on JobID.

 

[Marshall Barton]

The more I work with this program the more I realize how little I actually
know.

Some please give me a clear answer. I have two tables. One with Employees
and one with jobsites. Several of our employees work on more than one
project at a time. I am trying to generate a report that will list all of
the employees working on a project (when the user enters the appropriate
project #). The problem I have is that I have not been able to build my
expression in a way that will work. I either end up with one name because
only one person on that project is working on 2 projects, or I end up with
none.

My employee table has the fields current and 2nd, while the jobsite table
has the field project #, this project number field is the common factor, both
the current and 2nd fields on the employee table use the project #s listed on
the jobsite table. So how do I write this expression that will tell the
query to look into the current field and then look into the 2nd field without
excluding everyone not listed in both fields?

Your table design is flawed. You should not have the
project number in the employee table. Instead you should
have what's called a junction table that has two (or more)
fields, one for the employee id and the other with the
projuct number. This is how a many-to-many relationship
should be done because it eliminates the confusion that is
causing you trouble and it also allows you to have as many
or as few projects per employee as needed.

Please do yourself a favor and fix your table design before
you run into all the other problems caused by unnormalized
tables.

If you can't fix the big problem, this current issue can be
gotten around by using an OR in the WHERE clause. In the
query design grid, that can be expressed by placing the
criteria for one field in the Criteria row and the other
field's criteria in the Or row.