[AccessD] Duplicates Query

A.D. (et al):

Thank you.  Worked perfectly.  Efficiency was not too important as it was a
one time run from converting some data imported from another system and the
amount of data was small - 4 fields, <1000 records.

Best,

Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com
www.bchacc.com
 
 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D. Tejpal
Sent: Wednesday, September 01, 2010 10:32 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Duplicates Query

Rocky,

    You could also try two other alternatives as per sample queries given
below, for removing all duplicates beyond the first occurrence. F1 to F4 are
the names of four fields, combined contents of which, determine the
duplicate status. ID is the name of PK field.

    Query Q_DEL_DupsBeyondFirst_ByNotInTop1 is expected to be more efficient
as compared to Q_DEL_DupsBeyondFirst_ByCount. (There would be only one
record featuring in the Not In clause).

    Note: 
    (a) Individual comparison of fields, one at a time, has been preferred
over using their concatenated contents, so as to eliminate the risk of
inconsistent results arising out of potential sliding match. Moreover,
contrived concatenation can detract from optimum performance.

    (b) Use of Nz() function while comparing the field values, eliminating
the potential for erratic results arising out of Null values getting
compared via = operator. 

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

Q_DEL_DupsBeyondFirst_ByNotInTop1
==========================================
DELETE *
FROM T_Data
WHERE T_Data.ID Not In (SELECT TOP 1 ID FROM T_Data AS T  WHERE Nz(T.F1, "")
=  Nz(T_Data.F1, "") AND Nz(T.F2, "") =  Nz(T_Data.F2, "") AND Nz(T.F3, "")
=  Nz(T_Data.F3, "") AND Nz(T.F4, "") =  Nz(T_Data.F4, "") ORDER BY  ID);
==========================================

Q_DEL_DupsBeyondFirst_ByCount
==========================================
DELETE *
FROM T_Data
WHERE (SELECT  Count(*) FROM T_Data AS T  WHERE Nz(T.F1, "") =
Nz(T_Data.F1, "") AND Nz(T.F2, "") =  Nz(T_Data.F2, "") AND Nz(T.F3, "") =
Nz(T_Data.F3, "") AND Nz(T.F4, "") =  Nz(T_Data.F4, "")  AND T.ID <=
T_Data.ID) >  1; ==========================================

  ----- Original Message -----
  From: Gustav Brock
  To: accessd at databaseadvisors.com
  Sent: Wednesday, September 01, 2010 22:39
  Subject: Re: [AccessD] Duplicates Query


  Hi Rocky

  I believe you will have to group by these fields in a way to build a
unique string, say:

    Group By [Field1] & "-" & [Field2] & "-" & [Field3]

  /gustav


  >>> rockysmolin at bchacc.com 01-09-2010 18:56 >>>
  Gustav:

  There are actually three fields that have to match up to make a duplicate.
  I suppose I could add another field which is the concatenation of those
  three but that seems kludgey.  Any other more elegant solution?

  TIA

  Rocky


  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
  Sent: Wednesday, September 01, 2010 8:49 AM
  To: accessd at databaseadvisors.com
  Subject: Re: [AccessD] Duplicates Query

  Hi Rocky

  Use First or Last or Max or Min - just one that will return only one PK of
  the records where the field named "name" holds identical values.

  /gustav


  >>> rockysmolin at bchacc.com 01-09-2010 17:38 >>>
   Gustav:

  Looks right but it has step 2 as:

  2. Create a totals Query1 which shows GroupBy the name, and First of the
PK.

  I assume totals query = summation query.  But "GroupBy the name" - what
does
  'name' refer to?  And how to do First of the PK?

  TIA

  Rocky


  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
  Sent: Wednesday, September 01, 2010 12:16 AM
  To: accessd at databaseadvisors.com
  Subject: Re: [AccessD] Duplicates Query

  Hi Rocky

  Yes. Look up a not so old thread:
  http://databaseadvisors.com/pipermail/accessd/2009-May/068576.html 

  /gustav


  >>> rockysmolin at bchacc.com 31-08-2010 18:42 >>>
  Dear List:

  I used the find duplicates query wizard to make a query to find duplicate
  records in a table.  Is there a quick way to then delete all but one
  occurrence of the duplicated records?

  MTIA 

  Rocky Smolin

  Beach Access Software

  858-259-4334
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com