Problem displaying all records in junction table

[krini_pop]

Hello,
I have a many to many relationship between employees and skills. I
designed a main form based on employees and have a subform based on the
junction table. On the subform I have a combo box in the header that
allows a user to select a category. Based on the category, in the
details section of the form another combo box gets filtered data and
populates the junction table with the employee and skill. That works,
but the problem is that whenever I select a new category.
For example,
Categories: Programming Languages, Personal Characteristics
Under Programming languages you have C++, Java, HTML, etc
Under Personal Characteristics you have Reliable, Hardworking,
Trustworthy, etc

I select Programming languages for the category and in the next combo
box I get C++, Java and so forth. So I select C++ and it is displayed.
Then I select Personal Characteristics and the C++ record is not seen,
there is a blank combobox where it would be displayed like this

Record 1: <blank> where C++ should be
Record 2: Reliable

How can I get it to display all the records for an employee at the same
time and still keep the combo box filtering?

Thanks
Trina

 

[Allen Browne]

Create a query using the 2 main table without the junction table.
There will be no join between them, so you get every possible combination
(Cartesian product.)

Save that query, use it as a source "table" for another query.
Add the junction table as well.
Join the 2 on the main fields.
Double-click the lines joining the 2 "tables" in query design, to change
them to outer joins.

 

[krini_pop]

Thanks for the response. What I ended up doing was to have the record
source of the subform to be all the skills for the employee when
opened. When someone goes to select a skill category from the combo
box, the row source of the subform and the row source of the skill
combo box (2nd combo box on subform) change to take the skill id from
the category combo box. Therefore it only displays the relevant records
and allows the user to select skills only pertaining to that category.

Thanks
Trina