ACC2: Counter Starts at 2 in Table Created by Make-Table Query

• Microsoft Access version 2.0

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

If you run a make-table query with a criterion that has no matching records, the query creates an empty table. If you run an append query with a criterion that has no matching records, no records append to the destination table. In either case, if the destination table contains a Counter field, the next record you add to the table will have a counter value one number greater than you expect.

CAUSE

Microsoft Access version 2.0 provides the next value for Counter fields internally. Because it reserved a number for the first record added by the query, and the query did not add any records, the next value for the Counter field is one number greater.

RESOLUTION

View the make-table or append query in Datasheet view before you run it, to be sure the query returns records.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database NWIND.MDB.

2. Create a new query based on the Employees table.

3. On the Query menu, click Make Table. Type Empty Table in the Table Name box, and then click OK.

4. Drag the [Employee ID] and [Last Name] fields to the QBE grid.

5. Type <1 in the Criteria row for the [Employee ID] column.

6. Run the query. Note that a new, empty table called Empty Table is created.

7. Open the Empty Table table and type a name in the [Last Name] field. Note that the counter starts at 2 instead of 1.

REFERENCES

For more information about make-table queries, search the Help Index for "make-table query."