If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Auto Numbering
Hello,
I have almost completed my database, but I am having problems with the auto numbering. It's a project database which is assigned a unique project number each time a new project is added. On testing the database I added in some test records which I subsequently deleted, I now want to add real project using the next number available, but it skips out the numbers used for records deleted which I want to use again. Is there any way I can re-use these numbers??? Thanks for your help Andrea -- Andrea Stimson Technical Administrator |
#2
|
|||
|
|||
Auto Numbering
Rename the table containing the data.
Select the table and then copy. In the dialog box for new table name, enter the original table name and check Structure only. This creates an empty table with the same name and structure as the original. Then import the data from the original table. When satisfied that all data is imported correctly, you can delete the old table. -- Regards, OssieMac "Andrea Stimson" wrote: Hello, I have almost completed my database, but I am having problems with the auto numbering. It's a project database which is assigned a unique project number each time a new project is added. On testing the database I added in some test records which I subsequently deleted, I now want to add real project using the next number available, but it skips out the numbers used for records deleted which I want to use again. Is there any way I can re-use these numbers??? Thanks for your help Andrea -- Andrea Stimson Technical Administrator |
#3
|
|||
|
|||
Auto Numbering
Thanks for your help with this.
-- Andrea Stimson Technical Administrator "OssieMac" wrote: Rename the table containing the data. Select the table and then copy. In the dialog box for new table name, enter the original table name and check Structure only. This creates an empty table with the same name and structure as the original. Then import the data from the original table. When satisfied that all data is imported correctly, you can delete the old table. -- Regards, OssieMac "Andrea Stimson" wrote: Hello, I have almost completed my database, but I am having problems with the auto numbering. It's a project database which is assigned a unique project number each time a new project is added. On testing the database I added in some test records which I subsequently deleted, I now want to add real project using the next number available, but it skips out the numbers used for records deleted which I want to use again. Is there any way I can re-use these numbers??? Thanks for your help Andrea -- Andrea Stimson Technical Administrator |
#4
|
|||
|
|||
Auto Numbering
Hi Andrea,
I owe you and apology. My description was incorrect. I had exporting and importing to Excel on my mind. Just realized when I read your reply that I had given you the wrong info so try the foillowing. Create the new empty table as per my original post. Select Queries in the database window. Double click Create query in Design View. Select and Add the original table in the Show tables dialog and then close dialog box. Select all fields and drag into the query matrix. Click the drop down arrow beside the Query type button on the toolbar. Select Append query. In the dialog box, click the drop down arrow in the Table name field and select the new empty table name and click OK. Run the query (Red Question mark button on toolbar.) The new table should now be populated with the data from the original table. -- Regards, OssieMac "Andrea Stimson" wrote: Thanks for your help with this. -- Andrea Stimson Technical Administrator "OssieMac" wrote: Rename the table containing the data. Select the table and then copy. In the dialog box for new table name, enter the original table name and check Structure only. This creates an empty table with the same name and structure as the original. Then import the data from the original table. When satisfied that all data is imported correctly, you can delete the old table. -- Regards, OssieMac "Andrea Stimson" wrote: Hello, I have almost completed my database, but I am having problems with the auto numbering. It's a project database which is assigned a unique project number each time a new project is added. On testing the database I added in some test records which I subsequently deleted, I now want to add real project using the next number available, but it skips out the numbers used for records deleted which I want to use again. Is there any way I can re-use these numbers??? Thanks for your help Andrea -- Andrea Stimson Technical Administrator |
#5
|
|||
|
|||
Auto Numbering
If you are using the Access "Autonumber" data type, you need to be aware
that it is intended for use as a unique identifier. That means it should be unique, not sequential ... Autonumbers are generally unfit for human consumption. If you are displaying the autonumber to humans, someone's going to be confused (sooner or later). Instead, consider creating a "custom autonumber" (you can search on this term, or look at mvps.org/access for it) that WILL guarantee you have a sequential number. Regards Jeff Boyce Microsoft Office/Access MVP "Andrea Stimson" wrote in message ... Hello, I have almost completed my database, but I am having problems with the auto numbering. It's a project database which is assigned a unique project number each time a new project is added. On testing the database I added in some test records which I subsequently deleted, I now want to add real project using the next number available, but it skips out the numbers used for records deleted which I want to use again. Is there any way I can re-use these numbers??? Thanks for your help Andrea -- Andrea Stimson Technical Administrator |
#6
|
|||
|
|||
Auto Numbering
On Mon, 29 Sep 2008 02:23:01 -0700, Andrea Stimson
wrote: Hello, I have almost completed my database, but I am having problems with the auto numbering. It's a project database which is assigned a unique project number each time a new project is added. On testing the database I added in some test records which I subsequently deleted, I now want to add real project using the next number available, but it skips out the numbers used for records deleted which I want to use again. Is there any way I can re-use these numbers??? Thanks for your help Andrea Ossie's advice will help you salvage the existing data... BUT!!! Autonumbers have one purpose, and one purpose only: to provide a unique meaningless identifier for a record. They are *NOT* suitable for project numbers or for human consumption! They will always develop gaps; not only will a deleted record leave a permanent gap, but just hitting the ESC key after starting a record will "use up" an autonumber. If gaps aren't acceptable... simply don't use Autonumber. Instead, use a Long Integer field and a little bit of VBA code in your data entry form to assign a custom counter. The exact technique varies depending on your needs - search Google Groups for "custom counter" for many threads describing the options. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Auto Numbering
Hi again Andrea,
Jeff and John are absolutely right and I thought I should provide a little more advice. Backing up of your data before you start testing is imperative. I find that splitting the database so the tables are in a separate database is the best way. If you are unfamiliar with this than basically it is placing all the tables in one database (called the backend or be) and the rest of the project in another one (called the front end or fe). It is extremely easy to do. Look it up in help under split. You can then make a copy of the database with the tables to another backup folder and then do all the testing you want as well as modifications to the front end and then simply copy the original backend tables database back in and you have the original unmodified data. If during testing, you find that you want to make an alteration to a table then copy the original table database (be) back in and modify it and then make another backup before modifying data with testing. That way you still maintain the original data. Anyway, hope you find this helpful. -- Regards, OssieMac "John W. Vinson" wrote: On Mon, 29 Sep 2008 02:23:01 -0700, Andrea Stimson wrote: Hello, I have almost completed my database, but I am having problems with the auto numbering. It's a project database which is assigned a unique project number each time a new project is added. On testing the database I added in some test records which I subsequently deleted, I now want to add real project using the next number available, but it skips out the numbers used for records deleted which I want to use again. Is there any way I can re-use these numbers??? Thanks for your help Andrea Ossie's advice will help you salvage the existing data... BUT!!! Autonumbers have one purpose, and one purpose only: to provide a unique meaningless identifier for a record. They are *NOT* suitable for project numbers or for human consumption! They will always develop gaps; not only will a deleted record leave a permanent gap, but just hitting the ESC key after starting a record will "use up" an autonumber. If gaps aren't acceptable... simply don't use Autonumber. Instead, use a Long Integer field and a little bit of VBA code in your data entry form to assign a custom counter. The exact technique varies depending on your needs - search Google Groups for "custom counter" for many threads describing the options. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|