1

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?

5

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
  )
;
2

An alternative way, under the assumption that there are no repeated (Name, LastName) pairs, is the following.

First select all the names that appear more than once, and create a Query out of them.

This can be done with Access GUI by means of:

Design View with names that appear more than once

Once this is done, create another Query that JOINs this query with the original table, using Name as the common column:

Design View with the multiple names together with LastNames

And this is what you finally get (starting from your data)

Data view names / lastNames

The corresponding SQL:

NamesThatAppearMultipleTimes query:

SELECT t.Name
FROM t
GROUP BY t.Name
HAVING (((Count(t.ID))>1));

and NamesThatAppearMultipleTimes-and-LastNames

SELECT t.ID, t.Name, t.LastName
FROM NamesThatAppearMultipleTimes 
     INNER JOIN t ON NamesThatAppearMultipleTimes.Name = t.Name
ORDER BY t.Name, t.LastName;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.