Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    5

    Unanswered: Effectively using subqueries

    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:

    Code:
    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.

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    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.

    Code:
    Select distinct t.* FROM tblData t 
     inner join
    	   ( SELECT LastName FROM tblData 
      GROUP BY LastName 
      HAVING (Count(*)>1)
     ) as tm
     on t.LastName=tm.LastName
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Dec 2005
    Posts
    5
    I understand what you did and it works fine when I run it as a query, but when I try and apply it as a filter it doesn't work. It just returns all records.
    Last edited by Jake1111; 12-23-05 at 10:21.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •