ACC: Append Query Results in Duplicate Counter Numbers (94105)
The information in this article applies to:
- Microsoft Access 1.0
- Microsoft Access 1.1
- Microsoft Access 2.0
- Microsoft Access for Windows 95 7.0
- Microsoft Access 97
This article was previously published under Q94105
Moderate: Requires basic macro, coding, and interoperability skills.
SYMPTOMS
A table to which you append data by 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 a 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.
| Modification Type: | Major | Last Reviewed: | 5/9/2003 |
|---|
| Keywords: | kbprb kbusage KB94105 |
|---|
|