SELECT 'NOT IN' question

[gingernob]

Hi
I have 2 tables in this particular question:
1) Students
BCSID
SFIRST etc....

2) CandidateRef
CANDIDATEREFID
BCSID
EBID

Requirement:
I want to query Students, identifying records who are NOT IN CandidateRef, and EBID NOT=1, as the student record may exist but EBID may be = 2 and that is OK.

I used the Wizard that created the following SQL but can not make a distinction where EBID is concerned so I just get all student records that have not got a record in CandidateRef, but those records could be EBID=2 and they should not be excluded. Also I expected to see something like a 'NOT IN' statement but that is not what the Wizard created.

Current SQL:

SELECT Students.BCSID, Students.SFirst, Students.SOther, Students.SLast
FROM Students LEFT JOIN CANDIDATEREF ON Students.[BCSID] = CANDIDATEREF.[BCSID]
WHERE (((CANDIDATEREF.BCSID) Is Null));

Any ideas would be appreciated.
David

[patjones]

Are you saying that you want 1) all the people who appear in the Students without appearing at all in CandidateRef, plus 2) all the people in CandidateRef with EBID <> 1?

It makes sense to me if this is the case. However it would be impossible for a particular person to fulfill both these conditions.

Pat

[gingernob]

Kind of I guess:..best by example
students: BCSID, FULLNAME
1, fred bloggs
2, sam smith
3, arthur c clark
4, mickey mousa

candidateref: CID, BCSID, EBID
1, 2, 2
2, 4, 1

so I want the resulting select to return student records :
1, 2, 3 BUT NOT 4 as its EBID = 1

Am I making this more complicated than it really is???

[gingernob]

Finally got it!!!

SELECT Students.BCSID, Students.SFirst, Students.SOther, Students.SLast, Students.Sadd1, Students.SAdd2, Students.SCity, Students.SGovernate, Students.SHomePhone, Students.SMobile, Students.SEmail1, Students.SCHOOLID, Students.chkselected
FROM Students
WHERE (Students.BCSID) Not In (SELECT candidateref.bcsid
FROM candidateref
WHERE ((candidateref.EBID)=[Forms]![frmstudentbulkboardalloc]![cmbboard]));

Thanks. D

[patjones]

@gingernob
Happy to see that you got it!

Pat