Removing Duplicates

[Shaukat]

I have 3 "TYPES" of duplicates. Here is the table:

ID classID Attend_date
1 625 12/13/06
1 292 5/16/006

2 244 (empty/null)
2 260 (empty/null)
2 149 7/14/006

3 784 (empty/null)
3 395 (empty/null)
3 188 (empty/null)
3 976 (empty/null)

In type 1 records, the guy with ID 1, registered for 2 classes and attended
those classes. In type 2 records, the person with ID 2, registered for 3
classes but attended only one (at least one) class. In type 3 records, the
student with ID 3 registered for 4 classes but did not show up for any.
I want to write a query which picks up only the 3rd type of records: the
students who register for any number of classes (say, 1 to 8) but do not
attend any one of them. If they attend at least one of the classes they
registered in, they should not be picked up.
I will appreciate the help.
Shaukat

 

[Rob Parker]

This is easy if you use two queries. First, count the non-null attend_dates
for each student:

SELECT ID, Count(Attend_Date) AS CountOfAttend_Date
FROM TableName
GROUP BY ID
HAVING Count(Attend_Date) Is Not Null;

Save this as Query1; then use a query based on this to return only those
students with count of 0:

SELECT ID
FROM Query1
WHERE CountOfAttend_Date = 0;

It could probably be done with a sub-query, but I find it easier to set up
as two queries.

HTH,

Rob

 

[John Spencer]

Why not


SELECT ID, Count(Attend_Date) AS CountOfAttend_Date
FROM TableName
GROUP BY ID
HAVING Count(Attend_Date) = 0;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Rob Parker]

No reason at all ;-)

Except for a little brain fade :-(

Rob

 

[Shaukat]

Thank you very much Rob. I truely appreciate this help.

 

[Shaukat]

A million thanks for your kindness and the fact you cared to spend your time.