Re: How to check for duplicates?

From: Ignacio Vazquez (ivazquezATorioncommunications.com)
Date: 11/14/03 

Date: Fri, 14 Nov 2003 14:54:56 -0500

<Zoran> wrote in message 3fb5322e$1@newsgroups.borland.com...
> Before inserting a new record into the table I need to check for
> duplicates. Duplicate is if FirstName, LastName, Street, City, State, and
> ZIP are equal. I don't want to have combination of all these fields as an
> index.
>
> What would be the most elegant and fastest way to check this?

The most elegant and fastest way would be to create a unique index based on
those fields and catch the exception upon insertion. Since you don't want to
do that, the next way would be to do a SELECT COUNT(*) with those fields in
the WHERE clause. Doing a checksum for those fields 1) depends on what
database you're using, and 2) isn't necessarily faster than doing a
preliminary SELECT.

Cheers,
  Ignacio

Relevant Pages

  • Re: Copy Table data to another Table
    ... Easy way would be to run append queries from each emailed database to update ... To prevent duplicates you can set up an index on the field or fields which ... To set up a unique index ...
    (microsoft.public.access.externaldata)
  • Re: data / record duplication multiuser dbase
    ... A good database design prevents illogical duplicates with primary keys ... the client and volunteers form. ... Every table has its own primary key and unique index with autonumbering. ...
    (comp.databases.ms-access)
  • Re: DCount not recognizing criteria
    ... keeps inserting the record even when the patient id number has already been ... Each patient ID should only be in the Database once ... and no duplicates. ...
    (microsoft.public.access.formscoding)
  • Re: data / record duplication multiuser dbase
    ... Tom van Stiphout wrote: ... There is one table with clients, on table with volunteers and a table with appointments wich can be bound to a client and a volunteer. ... I meant that the primary key is autonumbering with index and no duplicates is on in the properties of the field. ... Is this what you meant with unique index. ...
    (comp.databases.ms-access)
  • Re: Check for Many Duplicate SSNs
    ... no records can be added that have a duplicate SSN as long as the ... unique index is set. ... Set the value to Yes (No Duplicates). ... > list the duplicates found before running the append query? ...
    (microsoft.public.access.formscoding)