Display Certain Records for Many to Many Relationship

[krini_pop]

Hello,

I have a many to many relationship between the employee table and
skills table. I have a main form based on the employee table and a
subform based on the junction table. There is a combo box on the
subform that displays the skill name instead of the id from the
junction table. I can add, delete, and modify records just fine. The
issue I am having is that each skill has a different level associated
with it. The level is in the skill table. So I updated the record
source for the combo box in the subform to put in a where clause (e.g.
where level_id=1). That shows the records, but it displays them like
this...

Name Jane Doe
Skills C++
<This is an empty combo box because there is another
skill that's for Jane Doe with a different level.>
Java

How do I keep that from happening? I only want the user to be able to
add and modify skills of a certain level.

Thank you
Trina

 

[Michel Walsh]

Hi,



Technically, I would suggest to add a WHERE clause to restrict the selection
to the skill levels you want:


SELECT Name, Skill
FROM myTable
WHERE (SkillID =1 AND Level =2) OR (Skill <> 1 AND Level = 1)


Sure, using a temp table can help a lot:

SELECT Name, Skill
FROM myTable INNER JOIN tempConstraint
ON myTable.SkillID = tempConstraint.SkillID AND myTable.Level =
tempConstraint.Level


where tempConstraint has 2 fields, the skillID and the level you are
interested for that skill.




Hoping it may help,
Vanderghast, Access MVP

 

[krini_pop]

The WHERE clause seemed to do the trick. The subform that was
originally based on the junction table is now based on the following
query
SELECT Junction_tbl.*
FROM Skill_tbl INNER JOIN Junction_tbl ON
Skill_tbl.ID=Junction_tbl.SkillID
WHERE Skill_tbl.Level=1;

Also added a where clause to the row source of the combo box to only
select skills with a level 1. Now the form displays all of the skills
for an employee that are level 1 and now a user can select additional
skills just for level 1.

I appreciate the tip. Thanks!