Link Tables

[Jamie Dickerson]

OK, I have two tables, Work Orders and Employees. In the WO table there are
several instances where an employee # is required (assigned to, recieved by,
completed by, etc). I am attempting to create a query/report that will list
all relevant information, but in place of the employee # I would like the
name to be visible. I created a relationship between the Employee table
(Employee #) and the WO table. When I run the query it only shows records
where the employee # is the same in all relevant fields. So unless the same
person requested, recieved, completed, etc. the WO the record is filtered
out. I have successfully done this before but only 1 field from each table
needed linked. Can anyone help me with this?

Thank You in Advance

Jamie

 

[John Spencer MVP]

You need to include several references to the Employees table in your query
and link each employee field in the Work Orders table to a different one of
the references. You don't need to make multiple tables, just add the Employee
table to the query several times - each reference will get a different name.
The alias names are usually something like
Employees
Employees_1
Employees_2
Employees_3

So link
WorkOrders.AssignedTo to Employees.EmployeeID
WorkOrders.ReceivedBy to Employees_1.EmployeeID
WorkOrders.CompletedBy to Employees_2.EmployeeID
WorkOrders.ReviewedBy to Employees_3.EmployeeID
etc.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jamie Dickerson]

Thank You John. You are life saver. I truly thought my head was going to
explode if I made one more unsuccesful attempt.

 

[Jamie Dickerson]

I hope you can help me with something else. I have 8 tables linked to 1 in
my query. I need to add one more. When I do this I get thousands of
duplicate records (ex. Record 1 is the only listed and it appears thousands
of times). As soon as I remove this last table link and the table the query
returns to normal. Am I at a link limit that I was not aware of? How do I
remedy this situation?

 

[John Spencer MVP]

It sounds as if you are adding the last table and not linking it to any table.
If so, you are getting a "cartesian" join. What that means is that for
every record in the table you have added you get one record in the all the
other tables.

So if TableA has one record and TableB has 500 records, you will get 500 rows
returned.

If TableA has ten records and TableB has 500 records, you will get 5,000 rows
(10 * 500) returned.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jamie Dickerson]

Fixed. Thanks Again. Your help has been invaluable.