Writing a SELECT query to return only records with duplicate values in one column

I know I am still learning SQL but I can't work out what I am doing wrong.

I have a table (tbcCrcFile). One of the columns (crcStrm) does not contain unique values. I want to return any records where their crcStrm value is not unique.

Below are all my attempts which I tried to adapt from googling. What do I need to change the query to, and why?

Attempt #1

PHP Code: 

10:15:21 AM: -2147467259 [Microsoft][ODBC Excel Driver] Cannot have aggregate function in WHERE clause (COUNT(tblA.crcStrm)>1).
10:15:21 AM: SELECT tblA.* FROM [tbcCrcFile$] tblA WHERE (COUNT(tblA.crcStrm) > 1) 


Attempt #2

PHP Code: 

10:16:38 AM: -2147217900 [Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression 'tblA.crcStrm WHERE (COUNT(tblA.crcStrm) > 1)'.
10:16:38 AM: SELECT tblA.* FROM [tbcCrcFile$] tblA GROUP BY tblA.crcStrm WHERE (COUNT(tblA.crcStrm) > 1) 


Attempt #3

PHP Code: 

10:19:00 AM: -2147217900 [Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression 'tblA.crcStrm WHERE COUNT(*) > 1'.
10:19:00 AM: SELECT tblA.*, COUNT(*) FROM [tbcCrcFile$] tblA GROUP BY tblA.crcStrm WHERE COUNT(*) > 1 


Attempt #4

PHP Code: 

10:24:43 AM: -2147217900 [Microsoft][ODBC Excel Driver] Cannot group on fields selected with '*' (tblA).
10:24:43 AM: SELECT tblA.*, COUNT(*) FROM [tbcCrcFile$] tblA GROUP BY tblA.crcStrm HAVING COUNT(*) > 1 


maybe

SELECT T.* FROM [tbcCrcFile$] T WHERE T.crcStrm = (SELECT TS.crcStrm FROM [tbcCrcFile$] TS GROUP BY TS.crcStrm HAVING (((Count(TS.crcStrm))>1)));

Originally Posted by JosephP

maybe

SELECT T.* FROM [tbcCrcFile$] T WHERE T.crcStrm = (SELECT TS.crcStrm FROM [tbcCrcFile$] TS GROUP BY TS.crcStrm HAVING (((Count(TS.crcStrm))>1)));

Good to hear from you again Josie

I tried your suggestion and got an error ( -2147467259 [Microsoft][ODBC Excel Driver] At most one record can be returned by this subquery. ) But I was sure that you were pointing me in the right direction so after googling the error message, I changed the query by one word 'IN' (i.e.

Please Login or Register  to view this content.

)

Then it worked perfectly.

I would never have solved this without your help. Thank you. And +1

Thanks again Josie. I had a need for something like this just now in a SQL Server query and I remembered this. Worked perfectly.