Two tables of same info

[Pixie78]

I had a tblDepartments table that contained DeptID as pk and DName as the
name of the department.
Through changes I had to make in my database, I realized I needed two of
these tables to be able to keep my other information separate.
Each employee is in a certain department. Then I have trainings that also
come from a specific department. In order to show that this employee from
this department, took these trainings from these departments, I had to create
2 different department tables with exactly the same info. I didn't see any
way around it. I started getting circular references or my queries just
weren't reflecting the correct information.
So now I have tblEmpDept for the employees which contains EDeptID (pk) and
EDept as name of the department.
I also have tblCourseDept for the trainings which contains CDeptID and CDept
which is exactly the same as the employees, except for the labels of CDept
and EDept.
First question is, Is there a better way of handling this or am I correct in
what I have done? It seems redundant to have two tables of the same
information but I need to use the info separately.
Second question, If this is correct, is there a way to update and edit both
tables at once? This information needs to remain consistent between the two
tables. I don't want my users to update one without updating the other but
it seems a pain to have to update the Employees departments and then do the
same thing to the Course departments. I just don't think they'd understand
why they have to update the same information twice.
Right now I just have a continuous form with an add, edit, and a delete
button but it's only linked to the employee's departments table.
Thank you for any help.

 

[SteveM]

You shouldn't have duplicate tables.

Did you know that you can add the table twice in your queries?
Then, on each one, you can set the relationship to the appropriate table to
get different data. When you do this, you will notice that the second one has
a number appended to the table name.

You can also do this in your relationships view to create self referencing
relationships.

Steve

 

[Pixie78]

Thanks Steve. I had changed so much around from the last time I tried what
you suggested that it actually worked this time. I had to run through and
change the references here and there but ultimately on my huge report..... I
have info from a couple of queries so it labeled the employee departments as
qryEmpInfo.dept and the course departments as being qryCourseInfo.dept and
this kept them separated in the big query where I needed to combine all the
info for the one big report. And also had to reference those controls on the
report the same way, instead of just dept. Hope that made sense for anyone
else who may have the same situation. But anyway, thanks so much for the
simple advice, I got it all running now! =)