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.