ACC2000: Append Query Results in Duplicate AutoNumbers

• Microsoft Access 2000

SYMPTOMS

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

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. The expected result is appended data with incremented numbers in the AutoNumber field.

CAUSE

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

RESOLUTION

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

MORE INFORMATION

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

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

Including fields with AutoNumber 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 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 field will be assigned a value of 56.

Steps to Reproduce Behavior

1. Open the Northwind sample database



2. Click Tables in the Database window.



3. Right-click the Shippers table and click Save As....



4. in Save As, click OK. A new table called Copy of Shippers should now appear in the Database window.



5. Open Copy of Shippers in Design View.



6. On the View menu, click Indexes.



7. Delete all rows from the Indexes dialog box.



8. Close the Copy of Shippers table and when prompted to save the changes, click Yes.



9. Click Queries in the Database window.



10. Click New, click Design View, and click OK.



11. In Show Table, click Shippers, click Add, and then click Close.



12. Add all fields to the QBE grid.



13. On the Query menu, click Append Query....



14. In the Append dialog box, select Copy of Shippers from the Table Name list, and click OK.



15. On the Query menu, click Run.



16. When warned that you are about to append rows to the table, click Yes to proceed.



17. Close the query (saving is optional) and click Tables in the Databases window.



18. Double-click Copy of Shippers and note the duplicate AutoNumbers.

Additional query words: prb

Keywords : kbusage kbdta QryMktbl
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb