Help with subqueries... aaarrggh!! (1 Viewer)

[CazB]

Access 2007.

I have a training database which logs all our employees and which training courses they have been enrolled on (plus when they attended, etc... )

What I am trying to do is to write a query which tells me which of our current employees has NOT been enrolled on a certain course.

I have a query which tells me who HAS been enrolled, and another one which gives me a list of all the Current Employees. This works, but I'm trying to tidy up my database and get rid of 'unnecessary' queries, so I thought I might be able to use a subquery with WHERE NOT EXISTS in order to remove the people who have been enrolled... but I can't get it to work, I get no one, and I know there should be 2.

This is what I've done:

qryEmpsEnrolled: (this is the one to tell me who HAS been registered, and returns 318 records)

SELECT qryGENAttendeeBase.AttendeeID
FROM qryGENAttendeeBase INNER JOIN qry_Registration ON qryGENAttendeeBase.AttendeeID = qry_Registration.AttendeeID
WHERE (((qry_Registration.EventTypeID)=215) AND ((qryGENAttendeeBase.CurrentAttendee)=True))
WITH OWNERACCESS OPTION;

qryEmpsCurrent: (this is the one to tell me who are Current Employees, and returns 320 records)

SELECT qryGENAttendeeBase.AttendeeID  
FROM qryGENAttendeeBase
WHERE (((qryGENAttendeeBase.CurrentAttendee)=True));

What I want to do is find out who the 2 people are who are 'missing' in qryEmpsEnrolled

I've tried writing the query with a subquery like this

SELECT qryGENAttendeeBase.AttendeeID
FROM qryGENAttendeeBase
WHERE (qryGENAttendeeBase.CurrentAttendee=True) AND (NOT Exists (SELECT qryGENAttendeeBase.AttendeeID
FROM qryGENAttendeeBase INNER JOIN qry_Registration ON qryGENAttendeeBase.AttendeeID = qry_Registration.AttendeeID
WHERE (qry_Registration.EventTypeID=215 AND qryGENAttendeeBase.CurrentAttendee=True)));

but I get 0... what am I doing wrong?

 

[jdraw]

There is an UnMatched Query in the Access Query Wizard- have you looked at it?

 

[CazB]

ignore me - I Fixed it!!
Stupid me had missed out the most important bit - marked in blue

I've now got

SELECT qryGENAttendeeBase.AttendeeID
FROM qryGENAttendeeBase
WHERE (qryGENAttendeeBase.CurrentAttendee=True) AND (NOT Exists (SELECT qry_Registration.AttendeeID
FROM qry_Registration
WHERE (((qry_Registration.EventTypeID)=215 [COLOR=blue]AND qryGENAttendeeBase.AttendeeID=qry_registration.attendeeID[/COLOR]))));