G
Guest
How do I make the Autonumber adjust automatically to a deleted record? when I delete on record it does not adjust the other records to reflect the new numbering.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Dirk Goldgar
SandTiger said:How do I make the Autonumber adjust automatically to a deleted
record? when I delete on record it does not adjust the other records
to reflect the new numbering.
You can't. That's not what autonumbers do, and it's not what
autonumbers are. Autonumbers are not "record numbers" -- there's no
such thing in a relational database -- they are system-generated unique
record identifiers. As such, they are suitable for use as primary keys,
and for linking one table to another by storing them as foreign keys in
the related table. If these keys were renumbered every time a record is
deleted, then the key-based links between tables would be broken.
You should not care what the value of an autonumber field is. Its sole
purpose is to provide a unique identifier. Don't think of it as a
record number.
S
Scott McDaniel
You can recover the deleted Autonumbers by performing a compact.
However, from your question it appears that you may be using Autonumbers
incorrectly. Autonumbers are arbitratily assigned by the database without
any regard to the data, and therefore autonumbers have absolutely no meaning
to the data in your tables. They are not consecutive (although they may
be).
If you need to consecutively number your records, you will have to build
your own functions which perform this. In Access, the DMax function can be
used to find the largest value in a particular column. You can then add 1 to
that value and store it as your sequence number.
--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net
new numbering.
However, from your question it appears that you may be using Autonumbers
incorrectly. Autonumbers are arbitratily assigned by the database without
any regard to the data, and therefore autonumbers have absolutely no meaning
to the data in your tables. They are not consecutive (although they may
be).
If you need to consecutively number your records, you will have to build
your own functions which perform this. In Access, the DMax function can be
used to find the largest value in a particular column. You can then add 1 to
that value and store it as your sequence number.
--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net
when I delete on record it does not adjust the other records to reflect theSandTiger said:How do I make the Autonumber adjust automatically to a deleted record?
new numbering.
G
Guest
Thanks for the reply Dirk.
So if I do need a record number that adjusts with each record added or deleted how should I go about doing that?
So if I do need a record number that adjusts with each record added or deleted how should I go about doing that?
S
Steve Schapel
SandTiger,
It is not possible to do what you ask. Which is a very good thing,
and is "by design". The purpose of an Autonumber field is to uniquely
identify each record. Generally, the actual number assigned to any
given record is not relevant. But these fields are frequently the
ones that are used as the basis of relationshps between tables. You
absolutely would not want the number to be changed.
Perhaps you could let us know a bit more detail about what you
actually want to achieve with this numbering, maybe with examples, and
someone will be able to advise accordingly.
- Steve Schapel, Microsoft Access MVP
It is not possible to do what you ask. Which is a very good thing,
and is "by design". The purpose of an Autonumber field is to uniquely
identify each record. Generally, the actual number assigned to any
given record is not relevant. But these fields are frequently the
ones that are used as the basis of relationshps between tables. You
absolutely would not want the number to be changed.
Perhaps you could let us know a bit more detail about what you
actually want to achieve with this numbering, maybe with examples, and
someone will be able to advise accordingly.
- Steve Schapel, Microsoft Access MVP
A
Arvin Meyer
when I delete on record it does not adjust the other records to reflect theSandTiger said:How do I make the Autonumber adjust automatically to a deleted record?
new numbering.
Autonumbers are unique numbers. That's all they are guaranteed to be. There
are no real life systems that self-adjust for deleted records, even a paper
system will lose a number when you destroy one.
In a relational database system, you never want to change a key anyway. That
is the only data which absolutely must remain the same. Autonumbers are most
often used as keys and have no meaning other than to the data. The only
advantage to having a sequential autonumber is for the developer to easily
sort on the records in their order of entry, while he is debugging.
If you are deleting all the records, or even the last few, compacting the
database will reset the number to the last highest number. So if you have
the following:
1, 2, 3, 4, 5, 6, 7 and delete 3, 6, and 7 then compact the database, your
next number will be 6. You still "lose" 3.
If you insist on having a sequential number, you'll have to code your own.
Be prepared for some serious coding to handle situations when someone
deletes a record in the middle of the data set.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
S
Steve Schapel
You can recover the deleted Autonumbers by performing a compact.
Only for Access 97 and earlier versions, and then only for deletions
at the "end" of the table.
- Steve Schapel, Microsoft Access MVP
A
Arvin Meyer
deleted how should I go about doing that?SandTIger said:Thanks for the reply Dirk.
So if I do need a record number that adjusts with each record added or
You'll have to write your own coded numbering system. It is possible, but
don't expect much help from us because we know that it is not the correct
thing to do in a relational database system. As I pointed out earlier, there
are no "real life" numbering systems that allow renumbering. Renumbering has
always been a manual thing to do.
Most of the people that care about sequential numbers are auditors, and they
only want to see what was destroyed. Business owners who are skimming
profits will try to fool auditors by renumbering with a second cash register
tape. It never fools anyone. As an auditor in a previous job, I can tell you
that the only thing that accomplishes is that it proves criminal intent. I
have caught every single person, without a single failure, who ever tried
it.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
G
Guest
Thanks for the replies guys. I really appretiate your help
What I'm trying to do is simply have a sequential number (1,2,3,...) for the customers who are added to our database. This number needs to be always in sequence even after we delete records so that we can always know how many customers we have at any given point
I'm just starting with Access so I made one big table with all the fields instead of multiple ones because I'm trying to keep it simple.(If you get a chance see my other question in "new users" about fields =). Scott said something about making my own function. How is that done? would anyone happen to have the code I need to do this? =
Happy holidays everyone, and thank you for your help.
What I'm trying to do is simply have a sequential number (1,2,3,...) for the customers who are added to our database. This number needs to be always in sequence even after we delete records so that we can always know how many customers we have at any given point
I'm just starting with Access so I made one big table with all the fields instead of multiple ones because I'm trying to keep it simple.(If you get a chance see my other question in "new users" about fields =). Scott said something about making my own function. How is that done? would anyone happen to have the code I need to do this? =
Happy holidays everyone, and thank you for your help.
G
Guest
Arvin said:If you insist on having a sequential number, you'll have to code your own.
Be prepared for some serious coding to handle situations when someone
deletes a record in the middle of the data set.
Really!!? It seemes such a simple thing to do. I'm surprised that no one else needs it or that they don't have a function already made to deal with something like this. Anyone has any more input?
S
Steve Schapel
SandTiger,
To re-iterate what others have said, what you are asking is
theoretically possible, but an invalid concept. The reason you give,
i.e. "so that we can always know how many customers we have at any
given point", can easily be achieved at any given point using a
Count() function applied to the table.
For the record, you can use this as the Default Value of your
numbering field on your data form to increment the numbers...
DMax("[YourNumberField]","YourTableName")+1
.... and then, as one possible idea, if you have a button on the form
to delete the record, code like this should re-number as required...
Dim Holder As Long
Holder = Me.YourNumberField
DoCmd.RunCommand acCmdDeleteRecord
CurrentDB.Execute "UPDATE YourTable _
SET YourNumberField = YourNumberField - 1 _
WHERE YourNumberField > Holder", dbFailOnError
.... but, as I said, I wouldn't do it, there's not much point.
- Steve Schapel, Microsoft Access MVP
To re-iterate what others have said, what you are asking is
theoretically possible, but an invalid concept. The reason you give,
i.e. "so that we can always know how many customers we have at any
given point", can easily be achieved at any given point using a
Count() function applied to the table.
For the record, you can use this as the Default Value of your
numbering field on your data form to increment the numbers...
DMax("[YourNumberField]","YourTableName")+1
.... and then, as one possible idea, if you have a button on the form
to delete the record, code like this should re-number as required...
Dim Holder As Long
Holder = Me.YourNumberField
DoCmd.RunCommand acCmdDeleteRecord
CurrentDB.Execute "UPDATE YourTable _
SET YourNumberField = YourNumberField - 1 _
WHERE YourNumberField > Holder", dbFailOnError
.... but, as I said, I wouldn't do it, there's not much point.
- Steve Schapel, Microsoft Access MVP
A
Arvin Meyer
Select Count(*) From tblCustomers;
in a query will give you the correct number of customers at any given point.
Just looking at the record selectors will also give you the right count.
One big table with all the field is NOT the correct way to do it. Before you
start and make a big mess for yourself, get a book (I suggest "Database
Design for Mere Mortals" by Michael J. Hernandez as a great starter book) or
at least read some articles from Microsoft on database design:
http://tinyurl.com/yr86m
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
in sequence even after we delete records so that we can always know how many
customers we have at any given point.
chance see my other question in "new users" about fields =). Scott said
something about making my own function. How is that done? would anyone
happen to have the code I need to do this? =)
in a query will give you the correct number of customers at any given point.
Just looking at the record selectors will also give you the right count.
One big table with all the field is NOT the correct way to do it. Before you
start and make a big mess for yourself, get a book (I suggest "Database
Design for Mere Mortals" by Michael J. Hernandez as a great starter book) or
at least read some articles from Microsoft on database design:
http://tinyurl.com/yr86m
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
the customers who are added to our database. This number needs to be alwaysSandTiger said:Thanks for the replies guys. I really appretiate your help.
What I'm trying to do is simply have a sequential number (1,2,3,...) for
in sequence even after we delete records so that we can always know how many
customers we have at any given point.
instead of multiple ones because I'm trying to keep it simple.(If you get aI'm just starting with Access so I made one big table with all the fields
chance see my other question in "new users" about fields =). Scott said
something about making my own function. How is that done? would anyone
happen to have the code I need to do this? =)
D
Dirk Goldgar
Steve Schapel said:Only for Access 97 and earlier versions, and then only for deletions
at the "end" of the table.
I think there was only a specific version of Access 2000 or of Jet that
wouldn't reclaim trailing autonumbers. I'm pretty sure that was fixed
in a subsequent release.
A
Arvin Meyer
else needs it or that they don't have a function already made to deal withSandTiger said:Really!!? It seemes such a simple thing to do. I'm surprised that no one
something like this. Anyone has any more input?
No one needs it because it isn't the right thing to do in a relational
database.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
D
Dirk Goldgar
SandTiger said:Thanks for the replies guys. I really appretiate your help.
What I'm trying to do is simply have a sequential number (1,2,3,...)
for the customers who are added to our database. This number needs
to be always in sequence even after we delete records so that we can
always know how many customers we have at any given point.
You don't need a sequential numbering system to do that. All you need
is a text box with a controlsource expression like this:
=DCount("*", "Customers")
where "Customers" is the name of your table. On a form based on that
table, you need only this as the controlsource expression:
=Count(*)
Or in a query, the SQL statement
SELECT Count(*) As CustomerCount FROM Customers;
will return the information.
S
Steve Schapel
I think there was only a specific version of Access 2000 or of Jet that
wouldn't reclaim trailing autonumbers. I'm pretty sure that was fixed
in a subsequent release.
Dirk, I just checked this in both Access 2000 SP3 and Access 2002 SP2
with Jet4 SP8, and it doesn't work for me! Certainly, deleting all
records from the table and compacting will "reset" the autonumber back
to 1 seed. But it doesn't reset the autonumber of a table with data
back to (Max + 1) like it does in Access 97.
- Steve Schapel, Microsoft Access MVP
S
Steve Schapel
SandTiger,
This is the first time I have ever heard of anybody wanting to do
this.
- Steve Schapel, Microsoft Access MVP
This is the first time I have ever heard of anybody wanting to do
this.
- Steve Schapel, Microsoft Access MVP
G
Guest
lol...ok guys I get it. I'm thinking about this all wrong. I followed one of the links on Arvin's sig and saw the "Ten Comandments"...I thkink I broke pretty much all of them in the last couple of days. =
I repent, and I now see the light. ;
Thanks for the help guys, I will be around posting more questions on the board, but first I'm gonna go do some reading on the internet
You guys are profissional, curtious, and very helpful...thats a rare thing to have on one board. =)
I repent, and I now see the light. ;
Thanks for the help guys, I will be around posting more questions on the board, but first I'm gonna go do some reading on the internet
You guys are profissional, curtious, and very helpful...thats a rare thing to have on one board. =)
J
Joe Fallon
Hi Arvin,
I once had a case where it did make sense to re-number records.
In a Union environment, we posted a Seniority list monthly.
As people retired during the month we had to re-number the seniority records
so that they were in sequence from 1 to N. (Note: this field was NOT the
primary key for the table. It was strictly for reporting purposes.)
I once had a case where it did make sense to re-number records.
In a Union environment, we posted a Seniority list monthly.
As people retired during the month we had to re-number the seniority records
so that they were in sequence from 1 to N. (Note: this field was NOT the
primary key for the table. It was strictly for reporting purposes.)
D
Derek
I have in the past encountered the same problem. I needed
a way to entre a new invoice number in every document
automatically. Unfortunateley I cannot remember how I did
it. But I know that it involved A query that ran and found
the highest number in the invoice table. I then added one
(1) to it in the invoice number field. I think it may have
been DLookup that was the key but I am sure you get my
drift. The problem when someone erases a record in the
middle of the record set is to my mind insermountable and
is probably why data backup is so important. If you simply
want to keep a record of how many records there are you
may find a way of using a record counter field. which
simply counts the number of records in a set.
a way to entre a new invoice number in every document
automatically. Unfortunateley I cannot remember how I did
it. But I know that it involved A query that ran and found
the highest number in the invoice table. I then added one
(1) to it in the invoice number field. I think it may have
been DLookup that was the key but I am sure you get my
drift. The problem when someone erases a record in the
middle of the record set is to my mind insermountable and
is probably why data backup is so important. If you simply
want to keep a record of how many records there are you
may find a way of using a record counter field. which
simply counts the number of records in a set.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.