Exception - (duplicate entry that are not matching)

[Dan]

Hi
I am struggling to create the following query.
Any idea?

I have a table with 3 fields that I need to analyse.
I need to find out a list of exception - people ( First_Name & Last_Name)
that have declared different distance from school - they can declare
duplicate but not different.

For example:

First_Name Last_Name Distance_School
Mark Hunter 10
John Dun 10
Mark Low 10
Lara Dun 10
David Guny 10
John Dun 10
Mark Low 5
Mark Hunter 10
John Dun 5
Lara Dun 10

Will need to give me the following 2 names:

John Dun (because he is sometimes 10, sometimes 5)
Mark Low (because he is sometimes 10, sometimes 5)

Many thanks,
Dan

 

[bcap]

(Untested)

SELECT DISTINCT First_Name, Last_Name
FROM my_table A INNER JOIN my_table B ON
(A.First_Name = B.First_Name AND
A.Last_Name = B.Last_Name AND
A.Distance_School <> B.Distance_School)

 

[bcap]

Ooops, correction:

SELECT DISTINCT A.First_Name, A.Last_Name
FROM my_table A INNER JOIN my_table B ON
(A.First_Name = B.First_Name AND
A.Last_Name = B.Last_Name AND
A.Distance_School <> B.Distance_School)

 

[raphaelguedj]

Ooops, correction:

SELECT DISTINCT A.First_Name, A.Last_Name
 FROM my_table A INNER JOIN my_table B ON
 (A.First_Name = B.First_Name AND
 A.Last_Name = B.Last_Name AND
 A.Distance_School <> B.Distance_School)








- Show quoted text -

Thnak you

 

[John Spencer]

This query will give you a list of those persons that have two (or more)
different values for distance.

SELECT First_Name, Last_Name
FROM [Your Table]
GROUP BY First_Name, Last_Name
HAVING Max(Distance_School) <> Min(Distance_School)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================