MS Access – Compare Two Rows and Find Differences

ms access

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?

Best Answer

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

Related Solutions

MS Access Query – Find Person by First and Last Name

I'm not familiar enough with Access' syntax to give you a cut-and-paste answer. In pseudo-code, though, you can use something of this form:

WHERE
    (   (Appointment.[First Name] = Forms![Search_frm]![FirstName_tbx].Value)
     OR (Forms![Search_frm]![FirstName_tbx].Value = <an empty value>))
AND (   (Appointment.[Last Name] = Forms![Search_frm]![LastName_tbx].Value)
     OR (Forms![Search_frm]![LastName_tbx].Value = <an empty value>))
AND (Appointment.Last4 LIKE Forms![Search_frm]![Last4_tbx].Value)
     OR (Forms![Search_frm]![Last4_tbx].Value = <an empty value>))

In the above replace <an empty value> with whatever Access delivers from your search field when you don't type anything there explicitly.

To force the user to enter at least one search field add another AND to the query (again, pseudocode):

AND (FirstName Is Not NULL OR LastName Is Not NUll OR Last4 Is Not Null)

Ms-access – Query – Match Name to Multiple Unique Types

-- this should work for a starter
select distinct MyTable.Name, MyTable.Type
from MyTable
where Name in (
        select t1.Name
        from (
            select t0.Name, t0.Type
            from (
                select distinct MyTable.Name, MyTable.Type
                from MyTable
                ) as t0
            group by t0.Name, t0.Type
            ) as t1
        group by t1.Name
        having count(*) > 1
        );

Best Answer

Related Solutions

MS Access Query – Find Person by First and Last Name

Ms-access – Query – Match Name to Multiple Unique Types

Related Question