Show all members with qual that comprises multiple competencies

[apkohn]

Hey all, this is a bit of a tricky one (well to me at least).

I have a database to record the training qualifications of members. To get a qualification, you have to pass all the designated competencies for that qualification (each qualification can have 1 or more competencies).

Now the database has the follwoing tables;
tblMember
MemberID

tblCompetency:
CompetencyID
CompetencyName

tblQualifications:
QualID
QualName

tblCompetencyForQual: (permits many-many linking of competency and qual)
CompetencyForQualID
QualID
CompetencyID

tblCompetencyAttained: (permits many-many linking of competency and member)
CompetencyAttainedID
CompetencyID
Member ID

Now, I want a query that will show all members that will consist of basically two columns, showing:
MemberID and QualificationAttained.

Is there a generic, SQL way to do this, or do I need to write a VB procedure to step through the info.

Thanks in advance.

 

[Remou]

If the number of CompetencyAttained equals the number of CompetencyForQual, does that mean the Qualifications has been attained? If not, how do you tell?

 

[apkohn]

Thanks Remou. I hadn't thout of comparing the number of comptencies attained in a qualification, to the total number required in a qualification. I was stuck in the mindset of thinkng:

For each competency in qualification, make sure the member has it.