Selecting duplicate fields from unique records in a single table

[dougmcc1]

How do you select duplicate fields from a single table? If the table looked like this:
a 5 b
c 5 d
e 5 f
g 6 h
z 4 y
I would need to select the first three records because they each have a duplicate field2 value.I'm thinking it would look something like this:
SELECT field1,field2,field3 FROM table WHERE field2 EXISTS (more than 1 time);
Or this:
SELECT field1,field2,field3 FROM table WHERE COUNT(SELECT field2 FROM table) > 1;
Thanks.

 

[sfreeman@co.mer]

OK... here you go.

SELECT
tblOne.Field1
, tblOne.Field2
, tblOne.Field3

FROM
tblOne

WHERE
(((tblOne.Field2) In (SELECT [Field2] FROM [tblOne] As Tmp GROUP BY [Field2] HAVING Count(*)>1 )))

ORDER BY tblOne.Field2;

 

[dougmcc1]

Thanks, that was my next guess

Just kidding. By the way, how would I pull records that have dupicate field1's AND field2's?

Thanks again.

 

[sfreeman@co.mer]

just put the same criteria for the first field...

SELECT
tblOne.Field1
, tblOne.Field2
, tblOne.Field3

FROM
tblOne

WHERE
(((tblOne.Field1) In (SELECT [Field1] FROM [tblOne] As Tmp1 GROUP BY [Field1] HAVING Count(*)>1 )) AND ((tblOne.Field2) In (SELECT [Field2] FROM [tblOne] As Tmp2 GROUP BY [Field2] HAVING Count(*)>1 )))

ORDER BY tblOne.Field1, tblOne.Field2;

 

[dougmcc1]

In the first code example, if you use "WHERE...IN..." it returns each row with a duplicate field twice. If you use "WHERE...NOT IN..." it doesn't return rows with duplicate fields at all.

How do you modify it to return all unique rows along with only one row with a duplicate field? Example:

a 1
b 1
c 2

What I want returned is:
a 1 (or b 1)
c 2

but "WHERE...IN..." returns:
a 1
b 1

and "WHERE NOT IN" returns:
c 2

Thanks.

 

[dougmcc1]

In other words, I need to return each row at least once but no more than once. The rows have 2 columns.

Thanks.