-=On 25 Jul 96, Mike Beijar said to All about autonumber:
 MB> I'm new using Access and I have either overlooked the info I'm
 MB> looking for, or just don't know where to look.
 MB> I'm building a table of customers (and several other tables in the
 MB> database) and want a unique identifier for each one (customer). I'm
 MB> presently looking at an ID field in the customer table which
 MB> automatically increments with each new customer. I would like to
 MB> start at, say, 10000 and increment from there, but autonumber seems
 MB> to only start at 0.
INF: Use Append Query to Set Initial Value of Counter Field
Article ID: Q94821
---------------------------------------------------------------------
The information in this article applies to:
 - Microsoft Access versions 1.0, 1.1, and 2.0
---------------------------------------------------------------------
SUMMARY
=======
You can change the starting value of a Counter field to a number other
than 1 by using an append query.
MORE INFORMATION
================
By design, Microsoft Access always numbers Counter fields beginning
with the number 1. You cannot edit a Counter field or change its
starting value.
However, you can force Microsoft Access to number a Counter field with
a number you choose by doing the following:
1. Create a new table containing a single Number field. Set FieldSize
   to Long Integer.
2. Give the new Number field a name identical to the name of the
   Counter field in your original table.
3. Use an append query to join this new record to your original table.
   This action forces Microsoft Access to number any new Counter
   fields with your number + 1.
For the expanded procedure, see the "How to Renumber the Counter
Field" section below.
Your original table has the following properties:
   Table: Receiving
   ---------------------
   FieldName: ID Number
      Data Type: Counter
   FieldName: Description
      Date Type: Text
How to Renumber the Counter Field
---------------------------------
1. Create a new table with the following structure and save it as
   Sending:
      Table: Sending
      -----------------------
      FieldName: ID Number
      Data Type: Number
      FieldSize: Long Integer
2. Open the Sending table in Datasheet view. Add a new record by
   inserting a value in the Number field. The value you enter must be
   1 less than the starting value you want for the Counter field in
   your original table. For example, if you want the Counter value in
   your original table to start at 100, enter 99 in the Number field.
3. Create a new append query to append the single record from the new
   table (Sending) to your original table (Receiving). To do this,
   choose Query, then choose New and select the Sending table. Save
   the query as AppendCounter.
4. From the Query menu, choose Append. Select Receiving from the
   Append To Table Name combo box, then choose OK.
   At this point, you have instructed Microsoft Access to append (or
   join) a field (or fields) from the Sending table to the Receiving
   table. Now you need to tell Microsoft Access which field or fields
   to append.
5. Append the ID Number field from the Sending table to the Receiving
   table either by selecting ID Number from the Field combo box in the
   query grid or by dragging the ID Number field from the Sending
   table to the query grid.
   Because Microsoft Access recognizes that the two fields in Sending
   and Receiving have identical names, it automatically places ID
   Number in the Append To field of the query grid, as follows:
      Query: AppendCounter
      --------------------
      FieldName: ID Number
      AppendTo: ID Number
6. Run the query by choosing the exclamation point (!) button on the
   toolbar. The following message is displayed:
      1 Row(s) will be appended.
   Choose OK to append the record to the Receiving table.
7. Delete the Sending table, then delete the newly appended row from
   the Receiving table.
   You are now ready to add a new record to your original table. The
   Counter will start at the number you have chosen.
   NOTE: Do not compact the database before adding a new record to the
   original table. If you do, Microsoft Access will reset the Counter
   value to the number 1.
REFERENCES
==========
For more information on append action queries and Counter fields,
search on the following words here in the Microsoft Knowledge Base:
   counter and append
For more information on customizing Counter fields, search on the
following words here in the Microsoft Knowledge Base:
   counter and custom
Microsoft Access "User's Guide," version 1.0, page 174
Additional reference words: 1.00 1.10 2.00 appending starting initial
KBCategory: kbusage
KBSubcategory: QryMktbl
Copyright 1993 Microsoft Corporation. All rights reserved.
--- Win-Mail v0.10a/BETA
---------------