Query to combine data from a table and a lookup table

[accessLearn]

I have a table A in MS access that gets updated through an access form and form has 3 employeename fields
and 3 Dept fields
Emp1, dept1
Emp2, dept2
Emp3, dept3
I have 2 lookup tables:
Employee table to store employee info with emplid (auto num),name (text) and dept(num)
Also Dept table with Deptid(autonum) and Dept (text field) for the department info
I did create Emp1, Emp2 and Emp3 field in table “A” but initially not thought to create associated department but now I want to be able to look at the department as well
I was thinking to write a query to get the department but unable to join.

I was thinking
Select A.*, Employee.dept from A inner join Employee
On (A.emp1=employee.emplid or A.emp2=employee.emplid or A.emp1=employee.emplid)
But getting an error that Join expression is not supported.

How can I do that?



Thanks,
Blyzzard

[June7]

Multiple similar fields for same type of data is not a normalized data structure.

You don't use Or in a JOIN clause.

Use the query designer. Have to pull in the department table 3 times, join 1 of each to 1 of each deptX fields.

Why do you need deptX fields? Doesn't the employee table have the department employee is assigned to?

Should save EmployeeID, not employee name.