troubleshooting Question

How to create a Query(s) to exclude records based upon two criteria

Avatar of StagIraq
StagIraqFlag for United States of America asked on
Microsoft AccessSQL
9 Comments1 Solution259 ViewsLast Modified:
Now for the fun question: How to get a list of items for a group of people that excludes items that some people have already accomplished?

My db is syllabus events for students. I have table_students and table_syllabus events and table_student resume tracking what has been completed with grades.

Query to count syllabus events:
There are 41 events to accomplish in a syllabus
there are 10 students in that syllabus course
my total syllabus events to accomplish then is 410

Query to show what events have been acocmplished by whom:
three students have accomplished the first syllabus item
Name | Event | Complete
Bill      | ECON101 | C
Ted    |  Econ 101 | C
Jill      |  ECON 101 | C

Need query to exclude only these three events so that I can see in my combo box drop down only the events remaining.

The query to show events remaining should return 407 events.

I almost have this working with the following query I derived from the EE-forum:

SELECT qryStudSyllEventList.PeopleID, qryStudSyllEventList.Nickname, qryStudSyllEventList.UnitCode, qryStudSyllEventList.SyllEventId
FROM qryStudSyllEventListToExclude RIGHT JOIN qryStudSyllEventList ON qryStudSyllEventListToExclude.SyllEventId = qryStudSyllEventList.SyllEventId
WHERE (((Exists (SELECT Xclude.[SyllEventId] FROM [qryStudSyllEventList] AS Xclude WHERE Xclude.[SyllEventId] = [qryStudSyllEventList]![SyllEventId] AND Xclude.[SyllEventId] = [qryStudSyllEventListToExclude]![SyllEventId]))=False))

The problem is that this query will exclude ECON101 from the list so that it implies that all students have done ECON101.  Instead of returning 407 items it returns 400 items and "ECON101" is not selectable for those students who have not had it yet.

What is a better way to generate the list of syllabus items remaining?

I ask this because I would like to generate the generic 407 count list and then have my form combo box drop down criteria make it only show items remaining for the studentID on the form.  

Thanks,
Stag
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros