Find duplicate records on two fields

[steevp]

Hi,

Please excuse the newbie type question, but I am wracking my brains to
solve what should be a simple query.

I have three fields in a table F1, F2, F3. Any of the fields may
contain duplicate data, but I am only interested in where only the
first two fields match. I want to query and get only the records (all
three fields) where there are duplicates in the first two fields, e.g.

F1 F2 F3
1 2 3
1 2 4
1 2 4
2 3 4
2 3 4

Would return only, 1 2 3, 1 2 4 (because there are more than one row
with 1 2) and not any of the records with 2 3.

Hope this comes out clear.

Thanks in advance
Steve in UK

[Bob Quintal]

"steevp" <st******@hotmail.com> wrote in
news:11*********************@g14g2000cwa.googlegro ups.com:

Hi,

Please excuse the newbie type question, but I am wracking my
brains to solve what should be a simple query.

I have three fields in a table F1, F2, F3. Any of the fields
may contain duplicate data, but I am only interested in where
only the first two fields match. I want to query and get only
the records (all three fields) where there are duplicates in
the first two fields, e.g.

F1 F2 F3
1 2 3
1 2 4
1 2 4
2 3 4
2 3 4

Would return only, 1 2 3, 1 2 4 (because there are more than
one row with 1 2) and not any of the records with 2 3.

Hope this comes out clear.

Thanks in advance
Steve in UK

In the query, put two instances of the table. It will show the
table names as TableName and TableName_1. Right click on each
name and set the alias property to A on one table and B on the
other.

Join A.F1 to B.F1, A.F2 to B.F2. Set the criteria for a.F3 <>
B.F3

You will still have a problem with your two instances of 1-2-4,
which you can resolve by adding a criteria on the primary key
(which you have not shown) being different.

--
Bob Quintal

PA is y I've altered my email address.

[steevp]

Thanks Bob!
Just the job. Adding a DISTINCT got rid of the other duplicates. The
code is as below for reference:

SELECT DISTINCT A.F1, A.F2, A.F3
FROM table1 AS A INNER JOIN table2 AS B ON (A.F1 = B.F2) AND (A.F2 =
B.F2)
WHERE (((A.F3)<>b.F3));

regards