p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   Autonumber Query (http://p2p.wrox.com/showthread.php?t=44634)

Brendan Bartley June 29th, 2006 06:21 AM

Autonumber Query
Hi All
Is it possible to start an autonumber from a particular number.So instead of starting at 1 start at 1575


Brendan Bartley

SerranoG June 30th, 2006 07:03 AM

If you clear the table completely and then compact and repair the database, it'll always reset to 1. You cannot force it to start elsewhere nor guarantee that the numbers will always be sequential, especially when you start deleting data in the middle.

Autonumbers are not meant to hold any meaningful value at all. Their purpose is merely to set a unique identifier to the record so you can reference it. Therefore, you'd never worry about where it starts or if it's a "logical" number.

If you need a number that has meaning, create your own indexed field in the table and populate it yourself. Don't use autonumber.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

AccessMaster July 16th, 2006 02:14 AM

Yes, you can.. to do this:

Create the first table that contains the counter type field that you want to start on another number. Don't enter any records.Create a second table, with a single long integer number type field that has the same name as the counter field in the first table. Create a record in the second table by entering a number one less than the number you want to start the counter at in the first table. Create an append query, to append the one record in the second table to the first table, and run it Delete the second table, delete the record that you appended to the first table, and start entering data.

Alternative method:

You can make an Append query that just appends a value from a parameter, to just the auto-number field (as long as no other fields are required), e.g.:

PARAMETERS [Number] Long;
INSERT INTO tblAuto ( intNumber )
SELECT [Number] AS Expr1;

"Hi Tech Coach"
Access Based Accounting/Business Solutions developer.

All times are GMT -4. The time now is 12:44 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.