For Full versions of my videos or to join my mailing list go to : http://www.pcwebinars.com How to reset autonumber in Access 2013, Microsft Access 2010, and Access 2007 Access 2016 tutorial
http://www.youtube.com/user/TomFragale Tom Fragale - Expert Computer Consultant & Trainer Custom On-Site Trainings , Webinars, and Consulting available (215)280-1073 This video will show you How to reset the autonumber in Microsoft Access 2013, Access 2010, and Access 2007.
in this video I want to show you how to
reset an auto number so it doesn't just
have to start at one each time uh so I'm
going to go into the products table here
and go into design view you can see the
product ID is an order number so uh if
you notice down here it says increment
so that means it's going to start at one
and go to two and then three for the
next one and so on you can also make it
a random number and it'll make sure that
you always get a different number there
but when we do increment it really does
start at one I'm going to close that
window and in this case I don't have to
save
that so if I go into the products table
let's say in this case uh we are selling
um food and and Beverages and things
like that so let's say we'll say um a
product that's a 10 o
steak and you can see how it made the
product ID number one I'll pick a
supplier over here
and let's say we'll pick a
category all right and then I'll come
down here to product uh the second uh
record and let's say we're going to have
um chicken
breast and uh we'll pick a supplier over
here and we'll pick a a category over
here so you can see how the first record
ID became number one and the second one
became number two I'm going to close
that
window so what if you really wanted to
start that at number 3,000 let's use
that as an example you're going to make
a weird little query uh and the query
can uh actually change the order number
so I want to pick on the create
menu and we're going to come over to a
query
design and then we'll pick on the
products
table actually uh that's not correct
you're you're actually going to uh don't
pick any table in the
beginning just leave it as a b query uh
now you're going to make it into a pin
query uh so that's going to be the key
step you're going to make it into the
pen query and then here is where you
pick the pro uh the products table or
the table that's in question all right
so I'll click on
okay
now you want to pick the field here that
is the field that is the order number so
in this
case where it says append to uh we're
going to pick under the product ID and
then up here on the field row you type
in the number that you want to start the
order number at so let's say I want to
type start at at 3,000 okay so you
really make this query in aend query you
append it to the table whose Auto number
you want to change down here where it
says append to you pick the actual field
that is the auto a number and then here
on the field row you um put the number
that you want to start the table at so
at this point we're going to run the
query and it says we're about to append
one row and that's correct that's what
should be happening I'm going to click
on yes
there and now that's really the end of
the query now you can save that query or
not maybe you have to run that in the
future so I'm going to go ahead and
close that and I'll save it and I'll
call this one
modify Auto number
now let's take a look at the products
table now notice how um you do have
record 3000 in there now by the way
that's something that you would do even
before there's any records there and I
can even show that it would work with or
without a record so when I go to the
next record let's say in this case we're
going to also have um
salmon notice how salmon became
30001 you can really delete 3,000 if you
wanted to uh it's not that important but
um the um notice how salmon started on
record
3001 and let's pick a supply for that by
the way these are the tables that come
with access as the the north uh database
maybe you recognize those
so and then we're going to go ahead and
uh make that uh Seafood and then the
next one let's say I also have a catfish
filets and uh let's say we're going to
get
that from this supplier over here and
we're going to also make that one see
through but more importantly look at the
order number that one's now
3002 uh now I want to demonstrate that
you can do this uh as soon as you make
the table so I'm going to close that
window now I'm going to copy the
table and uh we'll do a copy with a
paste but this time just going to copy
the structure
only and I'll call this one a new
products so we have the new products
table there uh you can see right now
it's an empty
table now I'm going to go ahead and do
the same kind of query except I'm going
to right click on that one and pick on
design
view and we're going to make sure that
we uh when we pick on append we're going
to append it to the new products
table and I'll click on okay
now let's say I want to start this one
at 5,000 so I'll change this to
5,000 and then we're going to run this
and it says you're about to append one
row and that's the end of the query I
can close that and save
it now if I go to the new products table
notice how it has record 5,000 which we
can delete however I'm going to go to
the new record and let's say in this
case um I'm also going to sell
brocc and then for the
supplier and we'll pick a
category okay notice how that record is
50001 I'm sure if we were to also sell
string beans then that would be
50002 so it's this interesting little
query it's a little aen query and that
is actually how you can reset the Auto
number in a table here in Microsoft
Access
