Table numerically fragmented! 1

[diverjess]

Please help

I have a table where the primary key is an autonumber. Multiple records within the table have been deleted and I would like to get rid of those empty spots (so that the deleted record's following record will take on that number and all following records' PKs will drop by 1).

I've tried compacting the database by selecting "Compact and Repair Database" from the "Tools" menu, but it will not reclaim all the spaces marked as deleted and rewrite the data in the table in primary key order.

Isn't this action supposed to do what I'm asking? What am I doing wrong or what can I do differently?

Thanks much!
~Jess

 

[CJTyo]

The point of the autogenerated number is that when a record is deleted that number is gone. You may want to have a manually entered number where you can keep things in some kind of numerical order by reassigning a number when a record has been deleted.

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[diverjess]

Bummer. So there is NO other way I can go about doing this?

 

[CJTyo]

There's no way to fill in those blanks if there's data, no. It's a very common question.

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[JeremyNYC]

Jess,

The point is, you should not attach _any_ importance to an autonumber. It is not meant to tell you any information except the identity of the record. If you need a field to hold other information, just make one.

Because of the nature of autonumbers, it is quite common to hide them entirely from users. I more often just choose to do some education with my users so that they know that they are not sequential, merely unique.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[MelissaT]

I have used a combination of queries and code to create my own "autonumber" that won't skip any numbers. It is good if you want to have a sequential numbering system without the user actually have to enter the number. I will post the solution next week, but it may end up being easier to E-mail you a small mdb to show you. Melissa
Designing Access databases since 1999

 

[diverjess]

I would love to see that! You can email the details to me or I'll look for your post next week...

Thanks Melissa, Jeremy and C!

 

[CJTyo]

Melissa,

I'd like to see it too, so would you mind posting it here?

Thanks,
C (stands for Cindy, by the way ;-) ) *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[MichaelRed]

The concept of a gapless autonumber is -as already stated- useless. What hasn't been stated is that is can actually be dangerious. If used to determine almost anything, it can easily give misleading results.

The more correct term (and process) to use in these situations is "Linked List", which can reasonably be implemented in SQL. The topic has been discussed in these forums in the past, and I have posted a couple of basic sample routines to manipulate such lists through vba/sql.

I think that those who consider using the concept of re-numbering (or gapless autonumbers) need to review the basic concepts / rules of relational databse operations. All who do so (and understand the review) will easily realize that autonumber is just a convenient way to generate a "Key" or index value which is only necessary or useful for it's relation to other records (in other tables). To change these is simply an inconvenient approach to scrambling the indicies.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MelissaT]

To agree with Michael, if you are using the autonumber just as a PK to link records and the users never see it, it doesn't matter if you lose a few of those autonumbers.
However, I have come across many departments that keep track of their records by an ascending number. They don't want to go back and look up the last number they used and type in it manually. I have found that the following works for those cases.

You'll have to have at least one record in the table with your beginning "autonumber" in order for this to work.

Create a query based on the table that your "autonumber" is in. Go to the view menu and click View Totals. Add the "autonumber" field to the query and select MAX instead of Group BY. On the field line, add a "+ 1" after your field name. Save the query.

On your form, change your "autonumber" field to a combo box with its' row source equal to your query. On the After
Update Event of the form, type:
Me.refresh

On the Got_Focus Event of your "autonumber" field type:
fieldname = fieldname.itemdata(0)
(Obviously, fill in fieldname with the name of your field)

Hope this helps. Please post if you have any questions. Melissa
Designing Access databases since 1999

 

[gsmithssg]

Melissa,
I am having problems updating the form. Me.Refresh does not seem to pull the next number after 1 record has been written to the table. I have the me.fresh on the form. I must be missing the how-to update the query. The form gives me an error as well - "MICROSOFT OFFICE ACCESS CAN'T FIND THE MACRO "ME" the Macro or Macro group doesn't exist or the macro is new and hasn't been saved."

Do I need to create a macro to update the query?