Compare Two Rows and Find the Difference Between Them Which is In The Second Column?

I have a table that has the following data:

ID      Name       LastName
1      William     Johns
2      John        Adam
4      William     Smith
3      Mark        Stephan

I want to Find all those records that their Names are the same but there Last Names are different. so the result will be something like this:

ID      Name       LasName
1       William     Johns
2       William     Smith

What would be the MS. Access Query or method to find all these records?

You could rephrase it like this:


Return every row for which another row exists with the same Name and a different LastName

which is very easy to translate to SQL if you are familiar with the EXISTS predicate:

SELECT
  *
FROM
  YourTable AS t1
WHERE
  EXISTS
  (
    SELECT
      *
    FROM
      YourTable AS t2
    WHERE
      t2.Name = t1.Name
      AND t2.LastName <> t1.LastName
  )
;

StackExchange Database Administrators Q#161738, answer score: 8