compare 2 fields in one table to 2 fields in another table to get

[Guest]

I have 2 tables--employee table and department table. Each with "division"
and "cost center" fields. I want to compare both fields in employee table
against both fields in department table to return the department value in
department table. How do I do this.
Thanks,
James

 

[John W. Vinson]

I have 2 tables--employee table and department table. Each with "division"
and "cost center" fields. I want to compare both fields in employee table
against both fields in department table to return the department value in
department table. How do I do this.
Thanks,
James

Why are you storing this data in both tables? Does an Employee belong
to a Department? If an employee works in a particular department, can
they be associated with a cost center or division that differs from
those of the department?

It sounds like your tables may not be correctly normalized, if you
have redundant data?

That said: you can create a Query. Add both tables; drag Division to
Division, and Cost Center to Cost Center. This will probably give "too
many" records if multiple employees and/or departments have the same
values.

John W. Vinson [MVP]

 

[Guest]

I am taking data from another program that stores codes for each
department(but not the actual department) by using division and cost center.
Then these have to be looked up on a printed out page to find out what dept.
It's a bad system but nontheless the system in place. What I want to do is
export the data from the stupid program into an access table(employee table)
then cross reference the division and cost center to get the appropriate
department. Each division/cost center combo gives a unique department.
There are about 40 departments. The division can be either A or B and the
cost center is any non-numeric character including symbols. So division A
and cost center % equals department 672. That's why I was setting up my
department table with a pre-populated division field and cost center field
that would be associated with the appropriate department field. I want
access to cross reference the division and cost center fields in the employee
table to the same values in the department table and then return the
appropriate department number.