Re: keeping duplicate records from being appended



Paul

One approach might be to create an index spanning the multiple fields you
don't want duplicated (Unique, no duplicates). When you run your append
query, you'll be informed that "n" records could not be added, blah, blah,
blah...

One way to prevent the warning is to turn the warnings off (either in a
macro or in code, SetWarnings = False).

WARNING! If you fail to turn the warnings back on after your append query,
Access will not tell you if something goes wrong. ALWAYS turn the warnings
back on!

--
Good luck

Jeff Boyce
<Access MVP>

"PaulFort" <PaulFort@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:551381E4-42CA-4DAE-839B-E9AED18B1884@xxxxxxxxxxxxxxxx
>
> How can I prevent duplicates from being appended (on the fly via an append
> query)
>
> to a table when the type of duplicates I want to prevent are entire record
>
> duplicates not just one-field-duplicates. (IOW: it's ok for all field 1
> values to
>
> include duplicates and it's ok for all field 2 values to include
duplicates
> but I
>
> don't want the append query to be able to append records where field 1 and
> field 2
>
> are the same as field 1 and field 2 of another record.)....and
>
> ...when there is this type of duplicate I want the append action to fail
>
> silently...so the user doesn't get an error. It just doesn't happen.
>
>
> BACKGROUND INFO
> I have an append query,activated by a button on a form (button runs macro
> that
>
> opens the append query) which appends records to a table. (There are three
> fields:
>
> name number, and color. Ultimately I need a list of numers and colors for
> each
>
> name..without duplicates. So I append all these fields for each record
from
> the
>
> original table that underlies the form to a table (table B)with the append
> query
>
> ..then a query whose properties are set to Unique Values = yes finds all
the
>
> numbers and colors for each name. This is the list without duplicates that
I
> need.)
>
> If the user hits the button more than once (without changing data) there
> will be
>
> duplicate records appended to table B. It could get pretty long if people
> get very
>
> happy with that button. I want to avoid having a very big table B in my
> database.
>
> Thanks much

.



Relevant Pages

  • Re: Action Query (Append) didnt add records due to key violations
    ... I created a union query based on the Append To table and the Append From ... duplicates matched the number of key violations from the Append query. ... I am using MS Access 2007 as front-end and SQL Server 2005 as back-end. ...
    (microsoft.public.access.queries)
  • Re: Automatically Delete duplicated Data from a table
    ... paste and a dialog box will come up and say append data ... only import the data that is not duplicates. ... >and run this query. ... copying and pasting in the Database window.. ...
    (microsoft.public.access.queries)
  • RE: append queries
    ... query is only pulling records for the last 6 months). ... And the Append Query: ... and I havent gotten any other warnings. ... The database file is 1.01G. ...
    (microsoft.public.access.queries)
  • RE: Deleting duplicate records
    ... duplicates in my table however only one record out of each of the duplicates ... When I run the append query I ... To append only unique records to the new table ...
    (microsoft.public.access.gettingstarted)
  • Re: Automatically Delete duplicated Data from a table
    ... Create the query (in ... INSERT INTO Table2 ... the fieldthat contained duplicates in the table you ... and then click Append Query. ...
    (microsoft.public.access.queries)