Finding same ID that has both values in another field

[sotssax]

Hello all,

I am trying to create a query that will find records with the same ID but with different values in another field.

I have a table with the following fields.
StudentID
ViolationDate
Offense

What I would like to do is find students that have only offense "A" and exclude the students that have only offense "B" or have both "A" and "B".

Any help will be greatly appreciated.

[Robeen]

Here's one way that works. It might not be the most sophisticated way to do this - but it works.
I believe it may be possible to do this using a 'Not Exists' in the Where Clause - using a dupe table with an alias name . . .

1. Create a query named 'QryStudents_With_B_Offense' that returns all Students who have a B Offense:

SELECT DISTINCT Offense.StudentID
FROM Offense
GROUP BY Offense.StudentID, Offense.ViolationDate, Offense.Offense
HAVING (((Offense.Offense)="B"));

2. Create a second query like this:

SELECT DISTINCT Offense.StudentID
FROM Offense LEFT JOIN QryStudents_With_B_Offense ON Offense.[StudentID] = QryStudents_With_B_Offense.[StudentID]
WHERE (((QryStudents_With_B_Offense.StudentID) Is Null));

When you run the second query - you should get all the students who have ONLY an A offense against their names.
I used 'Distinct' in both queries so that you only get each student name once.

Let me know if this helps.

[Robeen]

Sorry . . . double-posted.

[Robeen]

P.S. Once I said it could be done in a single query - using 'Not Exists - I HAD to try and make that work too.
Here it is all in one query [using a sub-query]:

SELECT Distinct Offense.StudentID
FROM Offense
Where Not Exists
(Select Distinct Dupe.StudentID
From Offense as Dupe
Where Dupe.StudentID = Offense.StudentID
and Dupe.Offense = "B");

Hope this helps!

[sotssax]

Robeen,

The second way worked. I didn't try the first one. Thanks for your help!!!!