[AccessD] deduplication

John,

    If you are in a position to clarify as to what type of output you are aiming at (by trying to convert the subquery into a stand-alone select query), a way could be found.

    The syntax error faced by you is explained by the fact that in the original SQL, the ">" operator was testing the sequential count of duplicate records as given by the output of subquery (whether more than one). 

    Now, with removal of subquery part as attempted by you, the WHERE clause has become mathematically flawed, missing an argument before the last operator. It looks like:
WHERE a = b AND c <= d  > 1;

    If you attempt to rectify this part by putting:
WHERE a = b AND c <= d;
you will overcome the syntax problem, but run into a new one, getting prompted for tblData.

    Moreover, as soon as you change the status from subquery to a stand alone select query, alias table T1 is no longer able to perform  dynamically out of synch with tblData. It is this dynamic feature, available via subquery,  that enables us to get sequential count.

A.D.Tejpal
---------------

  ----- Original Message ----- 
  From: jwcolby 
  To: 'Access Developers discussion and problem solving' 
  Sent: Tuesday, May 29, 2007 00:30
  Subject: Re: [AccessD] deduplication


  A.D.

  It seems to me that the subquery SELECT should be able to stand alone, and the subquery is giving a "Incorrect syntax near '>'" 

  John W. Colby
  Colby Consulting
  www.ColbyConsulting.com 

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
  Sent: Monday, May 28, 2007 1:29 PM
  To: Access Developers discussion and problem solving
  Subject: Re: [AccessD] deduplication

  John,

      Considering the table & field names mentioned by you, the SQL given below should get you the desired results. 

      You might like to try it out & confirm the outcome.

  Best wishes,
  A.D.Tejpal
  ---------------

  =====================================
  DELETE * FROM tblData
  WHERE (SELECT Count(*) FROM tblData As T1 WHERE T1.HashPerson =
  tblData.HashPerson AND T1.PKID <= tblData.PKID) > 1;
  =====================================

    ----- Original Message -----
    From: jwcolby
    To: 'Access Developers discussion and problem solving' 
    Sent: Monday, May 28, 2007 20:36
    Subject: Re: [AccessD] deduplication

    A.D. (or anyone willing to chime in)

    I am attempting to implement your solution.  I created the following
  select
    which should be the sub query in the outer WHERE:

    SELECT count(*)
    FROM tblData
    AS tblData1
    WHERE ((tblData1.HashPerson = tblData.HashPerson) AND (tblData1.PKID
    <=TblData.ID)) > 1; 

    However I am getting the very infamous Incorrect syntax near '>'.

    Any thoughts?

    John W. Colby
    Colby Consulting
    www.ColbyConsulting.com