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

StagIraq
StagIraq used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I can see what you are trying to achieve and how to achieve it.   However, it is difficult to provide a solution without having the table schemas available.

I can give you a general solution using my tables.  I assume you have similar tables in your solution.
PEOPLE:   PeopleID,  Name information etc.
EVENT: EventID,  Name information etc
PEOPLE_EVENT:  PeopleID, EventID,  Completed

PEOPLE_EVENT is the important one here - it holds the intersection between a person and an event.  In my solution, I have a Completed column to hold a Y/N where Y implies completion of the event.    You could also have a case where the presence of a record in this table implies completion.

So, the following query shows uncompleted people/event combinations:
Select Q.* from
(
Select P.PeopleID, E.EventID
From People P, Event E
) Q
Where not exists
(
  Select 1 from PEOPLE_EVENT
  where PeopleID=Q.PeopleId
  and EventId = Q.EventId
  and Completed = 'Y'
)


If you upload a sample database with the correct table names structures and existing queries you are working with,  I could post something a bit more specific to your case?
Hamed NasrRetired IT Professional

Commented:
Attach a sample database with dummy data. Limit the records to <10 that demonstrate the issue. List the expected output.

Author

Commented:
Fusion (& hnasr),

I built a copy of the db to upload like you requested, but apparently my internet connection from my deployed location is not swift enough to allow an upload to your site.  (ugh!)

So, I have tried my interpretation of (Fusion's) SQL.  As I am not overly familiar (euphemism for clueless on SQL) with SQL, I interpret what you wrote to be: Build a temp case/table called "Q", then show table Q excluding items where the person (PeopleID) and Event (SyllEventID) have not been Completed (Status) "C" OR previously Scheduled (Status) "S"

I input the actual table/field names below.  

SELECT Q.* (Select People.PeopleID, SyllabusEvents.SyllEventId
FROM People, SyllabusEvents) Q
Where Not Exists
(Select 1 FROM StudentResume
WHERE (((StudentResume.MemberID)=Q.PeopleID)) and ((StudentResume.SyllEventID)=Q.EventID)) and ((((StudentResume.EventStat)="C")) OR (((StudentResume.EventStat)="S"));

I hope you can slew through it to tell me where I have left out that little critical character that will allow this to run as you designed it...and not give me the following syntax error (that means nothing to me other than it didn't work).

Syntax Error in query expression 'Q.* (Select People.PeopleID, SyllabusEvents.SyllEventId
FROM People, SyllabusEvents) Q
Where Not Exists
(Select 1 FROM StudentResume
WHERE (((StudentResume.MemberID)=Q.PeopleID)) and ((StudentResume.SyllEventID)=Q.EventID)) and ((((StudentResume.EventStat)="C")) OR (((StudentResume.EventStat)="S"));'.

Thanks,
Stag

11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Hello?  I'd still appreciate help resolving this issue?  If my clarification attempt above is insufficient, please let me know and I will continue trying to get you what you need to help you help me.

Thanks,
StagIraq
Hamed NasrRetired IT Professional

Commented:
Add 'From' after Q.*

SELECT Q.* From (Select People.PeopleID, SyllabusEvents.SyllEventId

Complete sql and comment!

Author

Commented:
Thanks,  I updated the sql and the sql now reads as:

SELECT Q.* From (Select People.PeopleID, SyllabusEvents.SyllEventId
FROM People, SyllabusEvents) Q
Where Not Exists
(Select 1 FROM StudentResume
WHERE (((StudentResume.MemberID)=Q.PeopleID)) and ((StudentResume.SyllEventID)=Q.EventID)) and ((((StudentResume.EventStat)="C")) OR (((StudentResume.EventStat)="S"));

When run now the error reads:
 "syntax error in 'not exists'
(Select 1 FROM StudentResume
WHERE (((StudentResume.MemberID)=Q.PeopleID)) and ((StudentResume.SyllEventID)=Q.EventID)) and ((((StudentResume.EventStat)="C")) OR (((StudentResume.EventStat)="S"));

Please advise.

StagIraq
Retired IT Professional
Commented:
Does this work without error?

SELECT Q.* From (Select People.PeopleID, SyllabusEvents.SyllEventId
FROM People, SyllabusEvents) Q
Where Not Exists
(Select 1 FROM StudentResume
WHERE StudentResume.MemberID=Q.PeopleID)

If it works try to add more criteria.

Author

Commented:
hnasr,

Great advice walking me through adding criteria.  I worked your suggestion and got the sql to work with the criteria I desired...I have NO idea when the program decided to change your "Where Not Exists" statement to the final solution of "Esists = False" but, hey, IT WORKS!!

Here is the final syntax that the program liked...near as I can tell it mainly did not like the parenthesis around each of the table.field names?!?

SELECT Q.*
FROM (Select People.PeopleID, SyllabusEvents.SyllEventId
FROM People, SyllabusEvents)  AS Q
WHERE (((Exists (Select 1 FROM StudentResume
WHERE StudentResume.MemberID=Q.PeopleID and studentresume.sylleventid=Q.sylleventid and (studentresume.eventstat="C" or studentresume.eventstat="S")))=False));


Now I just have to put this query into the row source for my Combo Box and I'm set!

StagIraq
Hamed NasrRetired IT Professional

Commented:
Glad it worked!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial