Join/junction table use here?

[MGF23]

Hi all,

I have a list of 10 departments. For each department I have a list of known associated defects that can occur in these departments. However, now and again a new defects may occur that will eventually need added to my list of defects.



Regarding my database form: I’m not sure if it is best to have a list of departments in one combo box and a second combo with a list of all possible defects. This way does not seem very refined and probably not user friendly if the user has to scroll through 200 odd defects hoping to find one defect entry that best matches.

It would make more sense to select the department first and then have access make only the defects common to that department available in the second defect combo box.

Would a join table between departments and defects be used here? If so could someone give me a quick overview regarding the keys? From what I have been reading my related tables look like this:

[Tbl defects] ...........[JNT_Defects_depts] .........[TblDepts]
DefectID (pk) ........DefectID(fk).................. DeptID (pk)
Defect name ............Deptid(fk) ........................Dept name
Defectid(fk)

I'm not sure how updatable this would be if i need to assign a newly discovered defect to a department whilst in form-view?

Thanks for your time - any pointers greatly appreciated

[isladogs]

If, as seems likely, a defect can occur in more than one department, you do need a junction table as described.

Scrolling through 200+ records isn't practical
I would definitely have two cascading combo boxes in your form.