ACC: Append Query Results in Duplicate Counter Numbers

 Last reviewed: May 28, 1997
Article ID: Q94105
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

A table to which you append data using an append query now has duplicated numbers in a field defined as an AutoNumber data type in Microsoft Access 7.0 and 97 or Counter data type in Microsoft Access 2.0 or earlier. The expected result is appended data with incremented numbers in the AutoNumber or Counter field.

CAUSE

Your append query is explicitly referencing the field with the AutoNumber or Counter data type. You are, in essence, overriding the automatic AutoNumber or Counter function that you specified in your table design. This may result in duplicate numbers.

RESOLUTION

Do not include a field with an AutoNumber or Counter data type in the Append To row of the QBE grid in your append query. By omitting this field in your append query, Microsoft Access will automatically generate incremental numbers in the appended table's AutoNumber or Counter field.

STATUS

This behavior is by design.

MORE INFORMATION

You will receive duplicates only if there is no Primary Key or Index (No) defined on the AutoNumber or Counter field in the table to which you are appending. By definition, primary keys are unique.

Including fields with AutoNumber or Counter data types in your append query may also result in noncontiguous numbers in the table to which you are appending. For example, if the table to which you are appending has values of 1 to 20 in the AutoNumber or Counter field, and the table from which you are appending has values of 20, 34, and 55, you will end up with one duplicate value. The next time you add a new row to the table to which you are appending, the AutoNumber or Counter field will be assigned a value of 56.

Keywords : kbusage QryMktbl
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbprb
Resolution Type : kbworkaround

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 28, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.