|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I have an AutoNumber field in my database but Access skipped some numbers;
how do I fix this? I want all the numbers to increment by "1"...instead, it
goes from 1-43 and 154-208. I tried to change the starting number of an
AutoNumber field but it didn't work for this particular case.
Any help would be appreciated.
|
|
On Wed, 27 Sep 2006 15:21:02 -0700, Amy
<Amy[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >I have an AutoNumber field in my database but Access skipped some numbers;
>how do I fix this? I want all the numbers to increment by "1"...instead, it
>goes from 1-43 and 154-208. I tried to change the starting number of an
>AutoNumber field but it didn't work for this particular case.
>
>Any help would be appreciated.
This is in the nature of autonumbers. An Autonumber has one purpose,
and one purpose ONLY: to provide a unique key. It is not designed or
implemented to be sequential, continuous, or gap-free. Deleting a
record will leave a gap; hitting <Esc> after starting to enter a
record will leave a gap; running an Append query (as you may have
done) will leave a gap, sometimes a very large one.
If the values of the numbers are intended for human consumption and
gaps are unwanted, *don't use Autonumber*. Instead use a Long Integer
field and VBA code in the Form that you use to update the table to
increment the number. If the ID is the only required field you can use
code like this in the form's BeforeInsert event:
Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[tablename]")) + 1
Me.Dirty = False ' write the new record to disk immediately
' to prevent another user from getting the same number
End Sub
John W. Vinson[MVP]
|
|
You don't. Access autonumbers are system-generated unique row identifiers.
They have no inherent meaning, may suffer "gaps", may even be random in
order, and are unfit for human consumption.
There are special circumstances and special users that would make displaying
Autonumbers to those users less risky, but rarely! Instead, if you need a
number to show a user, use a "custom autonumber" (not a real Autonumber,
just plays one on TV). You can find out more about how to do these via a
Google.com search on this phrase.
Regards
Jeff Boyce
Microsoft Office/Access MVP
"Amy" <Amy[ at ]discussions.microsoft.com> wrote in message
news:2419CF9C-D89D-4A68-A9C2-6C461B9DD98D[ at ]microsoft.com...
[Quoted Text] >I have an AutoNumber field in my database but Access skipped some numbers;
> how do I fix this? I want all the numbers to increment by "1"...instead,
> it
> goes from 1-43 and 154-208. I tried to change the starting number of an
> AutoNumber field but it didn't work for this particular case.
>
> Any help would be appreciated.
|
|
Amy,
It is best not to use autonumber, but if you want the numbers to go back to
1, 2, 3, ect... then you must copy the table (right click the table, select
copy then right click a blank area, select paste and paste structure only.
Make the name different than the original table) then open both old table and
new table. Copy and paste the original table's information (selecting the
entire field) into the structure only table. This will reset the autonumber
sequence. Then remove the old table and rename the structure only table to
the original tables name.
Tony M
"Amy" wrote:
[Quoted Text] > I have an AutoNumber field in my database but Access skipped some numbers;
> how do I fix this? I want all the numbers to increment by "1"...instead, it
> goes from 1-43 and 154-208. I tried to change the starting number of an
> AutoNumber field but it didn't work for this particular case.
>
> Any help would be appreciated.
|
|
Tony
Given the nature of relational databases (e.g., Access), "resetting" an
autonumber field in Table1 risks making related rows in Table2 into
"orphans" (i.e., their "parent"/autonumber is now missing from Table1).
Even worse, "resetting" autonumbers in Table1 can have the same effect on
related Table2 rows as switching birth certificates ... the kids have
parents, but NOT THEIRS!
Regards
Jeff Boyce
Microsoft Office/Access MVP
"TonyM" <TonyM[ at ]discussions.microsoft.com> wrote in message
news:72AAABB3-4EC3-49C5-8B49-1EEB46BDBB48[ at ]microsoft.com...
[Quoted Text] > Amy,
> It is best not to use autonumber, but if you want the numbers to go back
> to
> 1, 2, 3, ect... then you must copy the table (right click the table,
> select
> copy then right click a blank area, select paste and paste structure only.
> Make the name different than the original table) then open both old table
> and
> new table. Copy and paste the original table's information (selecting the
> entire field) into the structure only table. This will reset the
> autonumber
> sequence. Then remove the old table and rename the structure only table to
> the original tables name.
> Tony M
>
> "Amy" wrote:
>
>> I have an AutoNumber field in my database but Access skipped some
>> numbers;
>> how do I fix this? I want all the numbers to increment by "1"...instead,
>> it
>> goes from 1-43 and 154-208. I tried to change the starting number of an
>> AutoNumber field but it didn't work for this particular case.
>>
>> Any help would be appreciated.
|
|
I'm having a similar problem. I want to have autonumbers so that I can later
use those numbers to put only a designated number of records into a query.
For example, if I had a list of cars on sale, and I wanted cars made after
2000, if the 2000 cars started at record 12500, my criteria for this column
could read > 12500.
I have a make table query, but I'm not sure how to autonumber it. Is there a
better way?
Thank you,
Joshua
"John Vinson" wrote:
[Quoted Text] > On Wed, 27 Sep 2006 15:21:02 -0700, Amy > <Amy[ at ]discussions.microsoft.com> wrote: > > >I have an AutoNumber field in my database but Access skipped some numbers; > >how do I fix this? I want all the numbers to increment by "1"...instead, it > >goes from 1-43 and 154-208. I tried to change the starting number of an > >AutoNumber field but it didn't work for this particular case. > > > >Any help would be appreciated. > > This is in the nature of autonumbers. An Autonumber has one purpose, > and one purpose ONLY: to provide a unique key. It is not designed or > implemented to be sequential, continuous, or gap-free. Deleting a > record will leave a gap; hitting <Esc> after starting to enter a > record will leave a gap; running an Append query (as you may have > done) will leave a gap, sometimes a very large one. > > If the values of the numbers are intended for human consumption and > gaps are unwanted, *don't use Autonumber*. Instead use a Long Integer > field and VBA code in the Form that you use to update the table to > increment the number. If the ID is the only required field you can use > code like this in the form's BeforeInsert event: > > Private Sub Form_BeforeInsert(Cancel as Integer) > Me!txtID = NZ(DMax("[ID]", "[tablename]")) + 1 > Me.Dirty = False ' write the new record to disk immediately > ' to prevent another user from getting the same number > End Sub > > John W. Vinson[MVP] > |
|
Joshua
If you have "cars made after 2000" in your database, do you have a
"ManufactureDate" field? Use a query to find all those with a
ManufactureDate>12/31/1999. If you don't have a [ManufactureDate] field,
but want to know about when cars were manufactured, just add the new field!
Regards
Jeff Boyce
Microsoft Office/Access MVP
"Joshua" <Joshua[ at ]discussions.microsoft.com> wrote in message
news:C516F0A1-7AF7-4FF9-A869-79834E4D2A75[ at ]microsoft.com...
[Quoted Text] > I'm having a similar problem. I want to have autonumbers so that I can > later > use those numbers to put only a designated number of records into a query. > > For example, if I had a list of cars on sale, and I wanted cars made after > 2000, if the 2000 cars started at record 12500, my criteria for this > column > could read > 12500. > > I have a make table query, but I'm not sure how to autonumber it. Is there > a > better way? > > > Thank you, > Joshua > > "John Vinson" wrote: > >> On Wed, 27 Sep 2006 15:21:02 -0700, Amy >> <Amy[ at ]discussions.microsoft.com> wrote: >> >> >I have an AutoNumber field in my database but Access skipped some >> >numbers; >> >how do I fix this? I want all the numbers to increment by >> >"1"...instead, it >> >goes from 1-43 and 154-208. I tried to change the starting number of an >> >AutoNumber field but it didn't work for this particular case. >> > >> >Any help would be appreciated. >> >> This is in the nature of autonumbers. An Autonumber has one purpose, >> and one purpose ONLY: to provide a unique key. It is not designed or >> implemented to be sequential, continuous, or gap-free. Deleting a >> record will leave a gap; hitting <Esc> after starting to enter a >> record will leave a gap; running an Append query (as you may have >> done) will leave a gap, sometimes a very large one. >> >> If the values of the numbers are intended for human consumption and >> gaps are unwanted, *don't use Autonumber*. Instead use a Long Integer >> field and VBA code in the Form that you use to update the table to >> increment the number. If the ID is the only required field you can use >> code like this in the form's BeforeInsert event: >> >> Private Sub Form_BeforeInsert(Cancel as Integer) >> Me!txtID = NZ(DMax("[ID]", "[tablename]")) + 1 >> Me.Dirty = False ' write the new record to disk immediately >> ' to prevent another user from getting the same number >> End Sub >> >> John W. Vinson[MVP] >>
|
|
I apologize; I used the example about cars to illustrate the problem that I’m
having in an easier manner. The actual issue I’m having is with a mail
parsing system. I have a query table that eliminates customers based on
certain criterion and leaves me with 209,000 records. Because the people
using the program are not familiar with access, I want to have a user
interface where they can just type in the number of entries they want, hit
another button, and have that information sent to an excel file for them.
I’ve already got the macro sending the information to excel, but I’m not sure
how to 1) make sure they only get the first “X†entries of that query table
and 2) Make sure they don’t use the same “X†people over and over.
If you can help me with this I would VERY greatly appreciate it.
Joshua
|
|
To get the "first" ### rows, you need to tell Access how to sort! Then you
use the TOP property to get the first ### rows.
If you need to know which ones have been selected already, you'll need to
add a field to record that fact. A Yes/No field would be enough, unless you
need to know WHEN a row was selected (in which case use a Date/Time field).
You will need to write a procedure that:
selects the top ## records
exports those to Excel
updates those records in Access to show that they were selected
Potential issues ... if the routine breaks (e.g., Excel fails for some
reason), you will want to prevent the update. You'll need to work through
your error handling routines to make sure everything worked properly.
Regards
Jeff Boyce
Microsoft Office/Access MVP
"Joshua" <Joshua[ at ]discussions.microsoft.com> wrote in message
news:A5CB067A-CFC9-47B7-806B-208B8FB7B69D[ at ]microsoft.com...
[Quoted Text] >I apologize; I used the example about cars to illustrate the problem that I'm
> having in an easier manner. The actual issue I'm having is with a mail
> parsing system. I have a query table that eliminates customers based on
> certain criterion and leaves me with 209,000 records. Because the people
> using the program are not familiar with access, I want to have a user
> interface where they can just type in the number of entries they want, hit
> another button, and have that information sent to an excel file for them.
> I've already got the macro sending the information to excel, but I'm not
> sure
> how to 1) make sure they only get the first "X" entries of that query
> table
> and 2) Make sure they don't use the same "X" people over and over.
>
> If you can help me with this I would VERY greatly appreciate it.
>
> Joshua
>
>
|
|
Thanks to everyone for their help. What I ended up doing, because I wasn't
too far into my creation, was just delete the column (from the table) that
used the AutoNumber. Then I went into the design view of the table and
created the same Autonumbered column again. This got rid of the gap in
numbers.
"Amy" wrote:
[Quoted Text] > I have an AutoNumber field in my database but Access skipped some numbers;
> how do I fix this? I want all the numbers to increment by "1"...instead, it
> goes from 1-43 and 154-208. I tried to change the starting number of an
> AutoNumber field but it didn't work for this particular case.
>
> Any help would be appreciated.
|
|
That worked perfectly. Thanks for your help.
Joshua Francis
"Jeff Boyce" wrote:
[Quoted Text] > To get the "first" ### rows, you need to tell Access how to sort! Then you
> use the TOP property to get the first ### rows.
>
> If you need to know which ones have been selected already, you'll need to
> add a field to record that fact. A Yes/No field would be enough, unless you
> need to know WHEN a row was selected (in which case use a Date/Time field).
>
> You will need to write a procedure that:
> selects the top ## records
> exports those to Excel
> updates those records in Access to show that they were selected
>
> Potential issues ... if the routine breaks (e.g., Excel fails for some
> reason), you will want to prevent the update. You'll need to work through
> your error handling routines to make sure everything worked properly.
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "Joshua" <Joshua[ at ]discussions.microsoft.com> wrote in message
> news:A5CB067A-CFC9-47B7-806B-208B8FB7B69D[ at ]microsoft.com...
> >I apologize; I used the example about cars to illustrate the problem that I'm
> > having in an easier manner. The actual issue I'm having is with a mail
> > parsing system. I have a query table that eliminates customers based on
> > certain criterion and leaves me with 209,000 records. Because the people
> > using the program are not familiar with access, I want to have a user
> > interface where they can just type in the number of entries they want, hit
> > another button, and have that information sent to an excel file for them.
> > I've already got the macro sending the information to excel, but I'm not
> > sure
> > how to 1) make sure they only get the first "X" entries of that query
> > table
> > and 2) Make sure they don't use the same "X" people over and over.
> >
> > If you can help me with this I would VERY greatly appreciate it.
> >
> > Joshua
> >
> >
>
>
>
|
|
Amy
....that solved your problem THIS time ...but it will most certainly reoccur
....if you need those numbers to remain sequential you need to use one of the
other solutions provided in the replies.
William Hindman
"Amy" <Amy[ at ]discussions.microsoft.com> wrote in message
news:15405E36-3A55-48C5-912D-E7E7224CB8D4[ at ]microsoft.com...
[Quoted Text] > Thanks to everyone for their help. What I ended up doing, because I wasn't
> too far into my creation, was just delete the column (from the table) that
> used the AutoNumber. Then I went into the design view of the table and
> created the same Autonumbered column again. This got rid of the gap in
> numbers.
>
> "Amy" wrote:
>
>> I have an AutoNumber field in my database but Access skipped some
>> numbers;
>> how do I fix this? I want all the numbers to increment by "1"...instead,
>> it
>> goes from 1-43 and 154-208. I tried to change the starting number of an
>> AutoNumber field but it didn't work for this particular case.
>>
>> Any help would be appreciated.
|
|
|