query problem

[pb21]

I have two queries. one shows enrolment events per student and the other withdrawn events.

Person, name, courseID,


for all the student courses that show as withdrawn in the withdrawn query I dont want to see in the enrolment query but not sure how to do this.

I do want to see the students other enrolments though just to remove the withdrawn rows.

heres what I have so far:

students that have withdrawn courses:

SELECT TblCourseStatement.PersonID, TblCourseStatement.FirstName, TblCourseStatement.LastName, TblCourseStatement.CourseCode, TblCourseStatement.CentreCode, TblCourseStatement.UpliftCode, TblCourseStatement.UpliftFactor, TblCourseStatement.EventType, TblCourseStatement.EventDate
FROM TblCourseStatement
WHERE (((TblCourseStatement.EventType) Like "WD"));

Students enrolments:

SELECT TblCourseStatement.PersonID, TblCourseStatement.FirstName, TblCourseStatement.LastName, TblCourseStatement.CourseCode, TblCourseStatement.CentreCode, Int([UpliftCode]) AS [Uplift Category], TblCourseStatement.UpliftFactor, TblCourseStatement.EventType, DateValue([EventDate]) AS [Date]
FROM TblCourseStatement
WHERE (((Int([UpliftCode]))<>99 And (Int([UpliftCode]))<>1 And (Int([UpliftCode]))<>96) AND ((TblCourseStatement.EventType) Like "ENR"))
ORDER BY TblCourseStatement.CentreCode;


regards in advance

 

[antomack]

Just want to clarify a few things first to see if I understand how the table is populated.

When a student enrols for a course there is a record entered with an EventType of 'ENR'
If this student subsequently withdraws from the course there is a second record added with an EventType of 'WD'

If this is how the table works then the following should give you all students who enrolled for a course and are still enrolled.

Essentially you are looking for all records in your enrolments query that do not have a corresponding record in the withdrawn query. To do this you need to create an 'Unmatched' query between the two current queries, there is a wizard to step you through this.

Looking at the details of the SQL you have for your 2 queries your new query will be something like
SELECT Enrolments.*
FROM Enrolments LEFT JOIN Withdrawn ON (Enrolments.PersonID = Withdrawn.PersonID) AND (Enrolments.CourseCode = Withdrawn.CourseCode);