Hello,


how can I achieve the following:

I have 4 tables in a database

1) Cursus_stam
A table with possible courses

2) cursus_competentie
For each course there are competencies and these are stored in this table.

3) personeels_evaluaties
In this table are the competencies stored for each staff member

4) personeels_evaluatie_datums
After an evaluation the result of this evaluation is stored in this table
An evaluation can be positive or negative


Now I want a query that does the following

After choosing a course I want a list of members that are missing one or more competencies that are connected to a course and this in order of importance. (If a member A is missing 5 competencies and member B is missing 7 competencies then member B should be on the top.


See the attachment.

So for example:

Is I choose course “1” then you can see in table 2 that this course has three competencies (1,5,6)

Then in table 3 you can see that member 1 is missing competency 1, member 2 is missing 1 and 5 and member 3 is missing 6

Now this information should be compared with the information in table 3 where you can see that member 1 has completed competency 1.

So the list for this course should be member 2 and 3 with member 2 on the top.

Who can solve this problem?

Thanks in advance,
Sven.