CODEWITHSUNDEEP
ms-access
Paul
Paul

Reputation: 69

Identify records that meet a criteria

2 hours on this and I cannot figure out the way. Please help

+--------+------------+--------+
| Field1 |   Field2   | Field3 |
+--------+------------+--------+
| 000001 | 24/12/1989 | John   |
| 000001 | 25/12/1989 | Bob    |
| 000002 | 01/01/1960 | Jane   |
| 000003 | 01/01/1960 | Betty  |
| 000003 | 01/01/1960 | Sid    |
| 000004 | 15/06/1975 | Nancy  |
| 000005 | 16/05/1975 | Pete   |
+--------+------------+--------+

I need to identify the records like 1 and 2, i.e. where field1 = field1 but the ajoining field (field2) is not the same.

To clarify, I don't care about rows 4 and 5 as even though they have the same value in field 1, the value in field 2 is also the same.

I would like my output to be:

+--------+------------+--------+
| Field1 |   Field2   | Field3 |
+--------+------------+--------+
| 000001 | 24/12/1989 | John   |
| 000001 | 25/12/1989 | Bob    |
+--------+------------+--------+

Upvotes: 2

Views: 28

Answers (1)

Andre
Andre

Reputation: 27634

You can do a self-join and specify not only = but also <> to compare fields:

SELECT t1.*
FROM myTable AS t1 INNER JOIN myTable AS t2
ON t1.Field1 = t2.Field1 AND t1.Field2 <> t2.Field2

This only works in SQL view, not in the Query designer.

Upvotes: 2

Related Questions