Course Management - If Members have completed courses they are listed

[AdamN]

Good Day!



I am having trouble determining how to solve a problem with Access 2003.

I have a database that we have been tracking if a user has completed a course. We now want to use this data to determine if they are eligble for a promotion.

Example:

Member Course
User 1 course 1
User 2 course 1
User 1 course 2
User 3 course 3


What I want to determine is if a user hase completed specific courses that makes them eligibe to be promoted that the query outputs the list of members that are qualified for the promotion.

In the above example, having completed "course 1" and "course 2" would make a user eligible for promotion. In this case, User 1 is the only member that has met both criteria and would be the only user output by the query.

For the life of me I cannot determine how to do this. Any suggestions would be greatly appreciated.

Cheers,

Adam

[JoeM]

Assuming a table name of "tblCourseMgmt", this query should return all people eligible for a promotion based on your sample and criteria:

SELECT tblCourseMgmt.Member, Sum(IIf([tblCourseMgmt]![Course]="course 1",1,IIf([tblCourseMgmt]![Course]="course 2",10,0))) AS [Counter]
FROM tblCourseMgmt
GROUP BY tblCourseMgmt.Member
HAVING Sum(IIf([tblCourseMgmt]![Course]="course 1",1,IIf([tblCourseMgmt]![Course]="course 2",10,0)))=11;

See if you can see the logic I incorporated. If you do not understand what I did, let me know and I'll explain it.

[AdamN]

Wow, you just blew my mind. This process makes so much sense to me. You are assigning each course a value, and when all the courses have been taken, they sum to a specific value which you are checking for.

[JoeM]

You got it!!!

[AdamN]

Thank you so much!