Defects and departments table question

[topp]

Hello all,

I feel like it has just been a long day but cant seem to figure out how to solve what I believe to be a simple issue. I have a department table and defect code table, with the defects belonging in "certain" departments. I had created a FK to put the department in and then realized that some defects appear in multiple departments. I created these tables so that the combo boxes on my form would show the appropriate defects after the user selected what department they were working in. After setting it up I have run into the issue of having duplicate defect codes in order to get it to show up when it belongs to multiple departments. Any help would be greatly appreciated.
Here are the two tables I created:
[Department]
[PK]DeptID
Department
[Defect Codes]
[PK]DefectID


Defect Desc
[FK]DeptID

[Micron]

It depends on what defects are but will assume here that they are static defined things since you called them codes. Then 2 primary tables - tblDepts and tblDefects, each not allowing duplicates of department or defect. You also need a junction table. Picture tblDepts on one side and tblDefects on the other with tblDeptDefects in between. This one is a many-to-many table; i.e. the same department can appear many times and the same defect can appear manytimes. Whether or not a combination can appear more than once is determined by your needs.