Hi, I have searched through this forum and haven't found the answer to a problem I am having so I figured I will post it.
My SQL statement:
Select * FROM tblData WHERE LastName=
(SELECT tblData.LastName FROM tblData GROUP BY tblData.LastName HAVING (Count(tblData.LastName)>1))"
What I am trying to do: I am trying to make an SQL filter that I can apply to my form in order to only show records with duplicate last names. The sub query returns names that are already in the table. I then compare what is found to be duplicate with the original table in order to just show only the duplicate records. Everything works fine as long as there is only one name thats duplicated.
When there are multiple duplicate names then I run into an error.
When the statement is put into a string and executed in VBA I get this error:
"Run-time error '3021': No current record."
When the statement is put into a query and run against the DB I get this error:
"At most one record can be returned by this subquery"
So yeah, any help would be greatly appreciated. Am I going about this all wrong or am I just forgetting something? Thanks for any help.
It is because ur subquery returns more than one value when u have more than one duplicates.This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Check the code below.
Select distinct t.* FROM tblData t
( SELECT LastName FROM tblData
GROUP BY LastName
) as tm
I love cursor,though I never use it.Cos' I dont want to misuse it.