Auto Number reset to 1

[AJOLSON]

How can I get an auto number field to reset to 1 when the contents of the
table is deleted?

 

[Douglas J. Steele]

Depending on what version of Access you're using, compacting the database
should reset the field.

Of course, there's really no need to. AutoNumber values aren't intended for
human consumption: they're intended to provide a (virtually guaranteed)
unique value that can be used as a primary key. That purpose is served by
2356, 2358, 2359 just as well as it is by 1, 3, 4.

 

[AJOLSON]

Very true however what I am loading is data from a external source that has
no unique characteristic to it, So what I want access to do is create a
unique Id for each file it loads however if the same file is loaded twice I
want it to create a duplicate Unique id so that the main table will engore
the duplicate.

I am going to combine one of the fields loaded with a unique Number
(starting at one) then So in essence I would have example “ 12-Dec-08-1†that
is created. If that file is loaded again 12-Dec-08-1 would be duplicated but
not added to the main table because no duplicate records are allowed. Now
each file can have duplicate dates but no two files will have duplicate
dates.

So my dilemma as it stands now. If the auto number field picks up where it
left off on the previous file load I would not create a duplicate record
hence leaving me with the real possibility of creating duplicate record, that
is very undesirable.

I am using access 2003

 

[Jeff Boyce]

As Douglas points out, the Autonumber is a unique identifier, in and of
itself. There's no need to concatenate a date with a unique number to get a
?unique identifier?!. Just use the unique identifier itself. Now, why does
it need to start with "1"? <g>

I'm guessing you are trying to have something a human could look at and make
sense of ... Autonumbers are generally unfit for human consumption (they
don't do what humans expect, they just provide a unique identifier).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Clifford Bass]

Hi,

Interesting situation. Is there some other way to uniquely identify a
particular row from the source file? Instead of creating a concatenated
field, just use two fields and place the combination of the two into a unique
index.

To reset the number back to one you have to run an append query that
appends into the autonumber field and that includes something for any
required columns:

INSERT INTO tblResetAutonumber (RecordID, DateField)
SELECT 0, #01/01/2000#;

You supply the value prior to the value you want for the autonumber
field. After you run the query, delete the row you just added.

Clifford Bass

Very true however what I am loading is data from a external source that has
no unique characteristic to it, So what I want access to do is create a
unique Id for each file it loads however if the same file is loaded twice I
want it to create a duplicate Unique id so that the main table will engore
the duplicate.

I am going to combine one of the fields loaded with a unique Number
(starting at one) then So in essence I would have example “ 12-Dec-08-1†that
is created. If that file is loaded again 12-Dec-08-1 would be duplicated but
not added to the main table because no duplicate records are allowed. Now
each file can have duplicate dates but no two files will have duplicate
dates.

So my dilemma as it stands now. If the auto number field picks up where it
left off on the previous file load I would not create a duplicate record
hence leaving me with the real possibility of creating duplicate record, that
is very undesirable.

I am using access 2003
[snip]

 

[AJOLSON]

Yes I agree within itself the auto number would normally be a unique
identifier. And if I were not building in a measure to insure duplicate
records were not loaded I could use just that. But Trust me when I say I
can’t.

If it were as simple and load a file add an auto number to it and be done
with it I would be very very happy. But not the case here.

Here is my example

For background on the data lest say within the data he loaded lets say there
are 4 fields Name, customer number, type of transition, and date of
transaction.

Now a customer can and normally do have more than one type of transactions a
day so there is the possibility that a name and subsequently the customer
number will occur twice. In fact within those fields there are no unique
Identifiers. There can be duplicate types of transactions, customer can have
the same type transactions in the same day So within this file there are No
unique Identifiers. However the next days transactions will be unique to
that day so from file to file one can say ‘For that File as compared to
another file the Date is Unique. (not unique records but unique to the
file).


But at the end of the day with the 4 available fields there is not
combination of fields that would give me a unique field.

Now lets put it into motion

Example:
Little Johnny loads a file in the database, it is all transactions that
occurred on Dec,1,2008. We will say that is 10 records. I use the method
you suggested an load it up and sure enough I now have 5 fields with a unique
number to each field. So numbers 1 – 10 Zulu Bravo Wooo hooo.

Now lest say Little Johnny goes out that night and gets Drunk as hooter
Brown and comes to work the next day with a Buzzzzzzz on. He is tired and
not paying attention so he grabs the same file he loaded yesterday and loads
it again today. 10 records Ten loads 11 – 20 are now assigned to the files
and access gladly take them in.

BUT WAIT I now have Duplicate records. But Drunk Johnny don’t know that,
and most importantly ACCESS does not catch the Drunken Johnny’s mistake. Now
Customers are billed twice, accounting is mad, The database is corrupt, Man
no good news there. Clearly that is undesirable. So using access adds number
feature alone does not work here. What to do what to do. Hummmm

Lets Try this.

Little Johnny loads a file in the database, it is all transactions that
occurred on Dec,1,2008. We will say that is 10 records. Several business
rule are applied and what I end up with is a table with the 10 records and a
field named Unique ID with text like Dec,1,2008-1 ~~~~~~~ through Dec,1 2008
-10. Ok so now bravo zulu No duplicates looking good so far.
Now lest say Little Johnny goes out that night and gets Drunk as hooter
Brown and comes to work the next day with a Buzzzzzzz on. He is tired and
not paying attention so he grabs the same file he loaded yesterday and loads
it again today. 10 records Ten loads However now that I have the auto
number reset to one when the number gets added in Instead of getting
Dec1,2009 11 though 20. I get Duplicate records or Dec-1-2008-1 through
Dec,1,2008-10. Business rules are applied and it is attempted to save these
duplicates in a subsequent table the table says NO WAY DRUNK Johnny you cant
have duplicate records. It rejects the files sends a Message box error
saying “Hey Drunk Johnny, you cant load this file again. Try using the
right file.†Johnny realizes his mistake loads the right file. Now the
customer his happy because they are not billed again, Accounting is happy
because it does not add bad info to the DB, The DB is not corrupt , and
little johhny saves his job.. IN fact all are happy because there are no
duplicate records. Much better outcome than above.


So now that I have proven a legitimate reason to wanting access 2003 auto
number to reset to 1 could you please pass along how to do that.


Thanks

Andy

 

[Jeff Boyce]

I may be mis-reading your description.

It sounds like you said that the combination of all 4 (name, customer umber,
type, date) needs to be unique.

So if you added a unique index on your table across all four of those
fields, when Drunk Johnny attempts to (re-)load the same data file, Access
notices that it already has that (those) combination(s) and refuses. Does
this accomplish what you're after?

(I'm not trying to be difficult nor withhold information. As my spouse
regularly reminds me, I am ... "linear". If I don't have enough information
to figure an answer, I ask for more...)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Clifford Bass]

Hi,

I have another suggestion which does not rely on the autonumber
feature. Others in the groups have stressed that you cannot always rely on
it to do exactly what you think. If the files are uniquely identifiable, and
it sounds like that is the case, how about recording the importation of the
file?. Do it something like this:

1) User clicks button or whatever to start load from file.
2) Check file-load-history table to see if file already loaded.
A) File not there
i) Start of transaction
ii) Import file
iii) If success
a) Insert file-loaded record
b) Commit transaction
iv) Something failed
a) Roll back the transaction
b) Something failed Johnny, try again
B) File there
i) Sorry drunk Johnny

Clifford Bass

 

[AJOLSON]

Her is the solution to this dilemma. Works like a charm

Thanks all for your help in getting me pointed in the right direction.

Andy

In order to force Microsoft Access to number an AutoNumber field with a
number you choose, follow these general steps below:

For a new table that contains no records, you can change the starting value
of an AutoNumber field that has its NewValues property set to Increment to a
number other than 1. For a table that contains records, you can also use this
procedure to change the next value assigned in an AutoNumber field to a new
number.

1. Create a temporary table with just one field, a Number field; set its
FieldSize property to Long Integer and give it the same name as the
AutoNumber field in the table whose value you want to change.
2. In Datasheet view, enter a value in the Number field of the temporary
table that is 1 less than the starting value you want for the AutoNumber
field. For example, if you want the AutoNumber field to start at 100, enter
99 in the Number field.
3. Create and run an append query to append the temporary table to the
table whose AutoNumber value you want to change.

Note: If your original table has a primary key, you must temporarily
remove the primary key before running the append query. Also, if your
original table contains fields that have the Required property set to Yes,
the Indexed property set to Yes (No Duplicates), or field and/or record
ValidationRule property settings that prevent Null entries in fields, you
must temporarily disable these settings.
4. Delete the temporary table.
5. Delete the record added by the append query.
6. If you had to disable property settings in step 3, return them to
their original settings.

When you enter a record in the remaining table, Microsoft Access uses an
AutoNumber field value 1 greater than the value you entered in the temporary
table.

Note: If you want to compact the database after changing the starting
AutoNumber value, make sure to add at least one record to the table first. If
you don't, when you compact the database, the AutoNumber value for the next
record added will be reset to 1 more than the highest previous value. For
example, if there were no records in the table when you reset the starting
value, compacting would set the AutoNumber value for the next record added to
1; if there were records in the table when you reset the starting value and
the highest previous value was 50, compacting would set the AutoNumber value
for the next record added to 51.

 

[a a r o n . k e m p f]

Jet can randomly reset autonumbers, it's not even guaranteed to be
sequential.

civilized people-- when they see bugs like this-- move to a real
database (like SQL Server for example)

 

[BruceM]

It seems to me you're going to a lot of trouble to force autonumber to
behave in a way of your choosing. It sounds to me as if the records being
inserted do not contain dates, but rather that the dates are inserted as the
record is being downloaded. Or maybe there are no dates. The scenario you
described was colorful and rather entertaining, but difficult to follow.
From what I can tell you need to run a rather complex series of steps every
day, steps that include revising the table design to remove the PK, then to
put it back. You may be able to hold it together as long as you never take
a day off, or you can train somebody else to open the hood and work on
things.
Good luck to you if it works, but if there are problems it may be better to
describe the situation and ask for ideas than to start with the requirement
that the autonumber be manipulated daily.

 

[Larry Linson]

Jet can randomly reset autonumbers,

That is simply not true.

it's not even guaranteed to be
sequential.

This is true -- it is part of the definition of Autonumber and has always
been so -- that Autonumbers are "not even" guaranteed to be sequential.

civilized people-- when they see bugs
like this-- move to a real database
(like SQL Server for example)

This poster did not report a bug. The poster asked a question.

Larry Linson
Microsoft Office Access MVP

 

[Clifford Bass]

Hi Robert,

Check out <http://support.microsoft.com/kb/287756> which gives a method
to reset the seed for the autonumber field using code instead of queries or
compacting and repairing.

Clifford Bass

 

[Robert S.]

Let me see if I can present an example of how I used the Autonumber feature
for "human consumption" and then retrn to the original question of resetting
the index.

Historically, when we have done upgrades to various applications, I would
extract the static and transactional tables before and after the upgrade
processes. I would load the "Pre" and "Post" tables into very simple two
column tables within Access (2003 for this time period). The first column
was an autonumber index, and the second column was a full line from the table
with no discernment for data breaks (in other words, it was just the string
of characters from that line of the table). The first check was to see if
the indexes for the "Pre" and "Post" tables were the same (otherwise I would
have either dropped lines or somehow added lines in the upgrade process). My
second check was a simple text comparision of line X from the "Pre" table and
the same line in the "Post" table. If they were the same, I was happy; if
they were different I had some work to do. Once my comparison was complete,
I would delete the contents of both tables, run a "compact and repair" (which
would reset the autonumber to "1") and then start the cycle over for the next
table.

In this instance, if I could not reset the autonumber and the indexes got
out of synch (because of added or dropped lines), my next cycle was
completely hosed. The "compact and repair" would reset the two tables to a
common reference point ("1") and I could start over. My problem is that in
Access 2007, the "compact and repair" no longer seems to reset the autonumber
index. My current "workaround" is to copy the structure to another table and
delete the first table.

Any thoughts?
Thanks in advance,

 

[Paul Shapiro]

Allen Browne's website has code for resetting Access autonumbers to the next
sequential value.
http://www.allenbrowne.com/ser-40.html

 

[Robert S.]

I just wanted to say, "Thanks," to both Paul and Clifford. Both of your
references were helpful. Unfortuneately, the KB article was more helpful for
me because it explicitly states that the issue is a "break" rather than
"working as designed."
Thanks again,

 

[Clifford Bass]

Hi Robert,

You are welcome!

Clifford Bass