Solved Starting Appended Data at 1

[GPGeorge]

@dullster

If you want the record ID to be kept in sequence and the sequence to be intact, then you can't use an autonumber. You need to manually control the sequence. I imagine this has been pointed out already. You also can't delete records, or you will get gaps.

While this is true in an active Access database application, you can restore AutoNumbers to the default origin values by emptying all tables, then doing a C&R.

Also, when the tables (and that includes all relevant tables) have been reset, you can use theDBGuy's suggestion to seed the default values for AutoNumbers if needed.

To me, this seems like the long way around the block though.

I would start with a template accdb with empty origin tables. Then, instead of futzing around with a used accdb by emptying, resetting and restarting, I would just make of a copy of that pristine template accdb.

 

[jwcolby54]

It turns out that you can intentionally "fill in the holes" in a table with an autonumbered field (caused by deleted records) by creating a query which appends a single record into that hole. Once you do so autonumber picks up, takes over and starts autonumbering from that number you appended +1. And when it hits existing records later, it will just jump over the existing records and continue creating autonumbers.

Or at least this worked ages ago when I did Access for a living.

As others have pointed out, some folks use the autonumber to "have meaning" about the sequence of orders or some such. Not a good idea IMHO for all the myriad of reasons being discussed in this thread. IMO the PK of a table should only have one meaning, and that is as the PK in the parent table and as the FK in the child table..

 

[Pat Hartman]

When i load new data. I have to go in and change ID numbers in specific Queries that would not have to do. If they always start at 1, then my ID in the queries should never change.

This is pretty scary for production data. I did have this situation for an app that I delivered with test data though so maybe that is what you are doing. And the only reason that was an issue was because there were external files associated with the test data and those were labeled with the EmployeeID so I had to ensure that my test data always had the correct ID to match the linked files. For production data, nothing should have to be hardcoded.


Anyway, C&R once the tables are empty should solve the problem all by itself. There is no reason to delete the relationships also. Alternatively, there is a function called Reseed which will reset the autonumber of a table on the fly if you ever need to do that. I've used that a couple of times over the years for tables that had been corrupted and were generating duplicate autonumbers.

And finally, there is always the potential for gaps when you use an autonumber even if you don't allow records to be deleted. An append query that fails will leave x autonumbers "empty" depending on how many rows the append query was trying to append.

 

[dullster]

This is pretty scary for production data. I did have this situation for an app that I delivered with test data though so maybe that is what you are doing. And the only reason that was an issue was because there were external files associated with the test data and those were labeled with the EmployeeID so I had to ensure that my test data always had the correct ID to match the linked files. For production data, nothing should have to be hardcoded.


Anyway, C&R once the tables are empty should solve the problem all by itself. There is no reason to delete the relationships also. Alternatively, there is a function called Reseed which will reset the autonumber of a table on the fly if you ever need to do that. I've used that a couple of times over the years for tables that had been corrupted and were generating duplicate autonumbers.

And finally, there is always the potential for gaps when you use an autonumber even if you don't allow records to be deleted. An append query that fails will leave x autonumbers "empty" depending on how many rows the append query was trying to append.

It's was a pain when I checked the forms and found them blank. Everytime a appended a new DB it would continue numbering where it left off and i had to go in and edit the queries. I think this should fix it. Next couple DB I append will be the test.

 

[dullster]

While this is true in an active Access database application, you can restore AutoNumbers to the default origin values by emptying all tables, then doing a C&R.

Also, when the tables (and that includes all relevant tables) have been reset, you can use theDBGuy's suggestion to seed the default values for AutoNumbers if needed.

To me, this seems like the long way around the block though.

I would start with a template accdb with empty origin tables. Then, instead of futzing around with a used accdb by emptying, resetting and restarting, I would just make of a copy of that pristine template accdb.

I have a template DB that is used for all appends. I did the C&R when the tables were empty but it did not reset the ID to 1. Everything is read said it should reset but it didn't. Thus why I will run the reset statement. Thank you.

 

[GPGeorge]

Spoiler:

I have a template DB that is used for all appends

. I did the C&R when the tables were empty but it did not reset the ID to 1.

Spoiler:

Everything is read said it should reset but it didn't. Thus why I will run the reset statement. Thank you.

What I'm suggesting is a template accdb with no records at all. You should be able to copy that empty accdb and then proceed from that copy. The template itself wouldn't have to be reset because it would never be used for anything other than that set up step. Am I missing something in the process that would make that not work?

 

[dullster]

What I'm suggesting is a template accdb with no records at all. You should be able to copy that empty accdb and then proceed from that copy. The template itself wouldn't have to be reset because it would never be used for anything other than that set up step. Am I missing something in the process that would make that not work?

No I don't think you're missing anything. I have a template database but for some reason when I append the tables they do not start at 1 again. I don't know if it's bringing in the number from the tables I'm appending, I don't know why it's not starting as 1. I don't understand it either. I've done everything that is recommended to do and it still wouldn't be set as 1. Now that I've done the reset I will see if that stays at 1 for all new appends.

 

[Pat Hartman]

Empty the tables. Run the C&R. Then just open one of the tables and type. Is the autonumber 1? If not, the db is probably corrupted and you should rebuild it.

 

[theDBguy]

Now that I've done the reset I will see if that stays at 1 for all new appends.

Please let us know how it goes. Good luck!

 

[DHookom]

I need the table to have the same ID numbers so I don't have to go in and change the ID numbers in 4 queries every time I append new data.

SELECT tblEmployees.EmployeeID, tblDemoClients.ClientFullName, tblEmployees.[Adjusted Basic Salary], [Amount from Column A]-(([Adjusted Basic Salary]-[tblpayrolltaxes].[Lower])*[Percent of Withholding]) AS [Net Wage], [Adjusted Basic Salary]-[Net Wage] AS [Net Wage1], IIf([Net Wage1]<0,0,[Net Wage1]) AS [Net Wage2], tblEmployees.[Number of Exempt], [tblEmployees].[Number of Exempt]*[tblpayrolltaxes].[Exemption credit] AS Exemptions, tblEmployees.[Marital Status], tblpayrolltaxes.MyUCA, tblEmployees.[Salary Pay Period]
FROM tblDemoClients INNER JOIN (tblpayrolltaxes INNER JOIN tblEmployees ON tblpayrolltaxes.[Marital Status] = tblEmployees.[Marital Status]) ON tblDemoClients.DemoClientID = tblEmployees.DemoClientID
WHERE (((tblEmployees.[Adjusted Basic Salary]) Between [tblpayrolltaxes].[Lower] And [tblpayrolltaxes].[Upper]) AND ((tblpayrolltaxes.MyUCA)=391))
ORDER BY tblEmployees.EmployeeID;

So is this

tblpayrolltaxes.MyUCA)=391

what you need to be the same?

 

[dullster]

So is this

tblpayrolltaxes.MyUCA)=391

what you need to be the same?

That is one of the queries. I have three others. I have a code wrote in the template to make that table start at 1 when I append data. When I have data to append next I will see if it's working the way it needs to be.

 

[Pat Hartman]

That wasn't the question. WHAT is so sacrosanct about MyUCA = 391? Surely there is a better option for how to identify this record. For example, if you have a table of companies and one of the companies is the company running the app, you might want to add an Owner flag or something like that so you can select using Where OwnerYN = true to get the return address or ID for a path to a logo, etc. You would need code to ensure that only a single record can have OwnerYN set to true.

 

[gemma-the-husky]

It turns out that you can intentionally "fill in the holes" in a table with an autonumbered field (caused by deleted records) by creating a query which appends a single record into that hole. Once you do so autonumber picks up, takes over and starts autonumbering from that number you appended +1. And when it hits existing records later, it will just jump over the existing records and continue creating autonumbers.

Or at least this worked ages ago when I did Access for a living.

As others have pointed out, some folks use the autonumber to "have meaning" about the sequence of orders or some such. Not a good idea IMHO for all the myriad of reasons being discussed in this thread. IMO the PK of a table should only have one meaning, and that is as the PK in the parent table and as the FK in the child table..

You can poke/stuff autonumber IDs into a table to fill gaps, but then your records won't be in the logical order that they should have been.

 

[GPGeorge]

Spoiler:

No I don't think you're missing anything. I

have a template database but for some reason when I append the tables they do not start at 1 again.

Spoiler:

I don't know if it's bringing in the number from the tables I'm appending, I don't know why it's not starting as 1. I don't understand it either. I've done everything that is recommended to do and it still wouldn't be set as 1. Now that I've done the reset I will see if that stays at 1 for all new appends.

I am not saying that you use the actual template database for new projects.

I am saying that you start with a new accdb set up with the tables you want. NO data in it at all, ever.

Put that pristine template in a safe location and never add data to it.

When you need to create a new project, create a copy of that pristine template. Again, do not use the pristine template itself for anything else, ever.

Use that new copy of the template for your next go-round.

When you are ready to start over with another new project, create another copy of the pristine template for that new project and use it.

Of course, you can go through the various resetting approaches: deleting, C&R'ing and so on if that appeals to you more.

 

[jwcolby54]

It turns out that you can intentionally "fill in the holes" in a table with an autonumbered field (caused by deleted records) by creating a query which appends a single record into that hole. Once you do so autonumber picks up, takes over and starts autonumbering from that number you appended +1. And when it hits existing records later, it will just jump over the existing records and continue creating autonumbers.

Or at least this worked ages ago when I did Access for a living.

You can poke/stuff autonumber IDs into a table to fill gaps, but then your records won't be in the logical order that they should have been.

I'm just saying that it can be done. Stuff can be good to understand without any other reason that understanding how things work.

Furthermore, if you are using the PK autonumber to mean anything other than "this is the PK of this record in this table" then you might want to rethink what you are doing. Applying some random "the order of the PK means XYZ" is probably not a good idea.

 

[GPGeorge]

You can poke/stuff autonumber IDs into a table to fill gaps, but then your records won't be in the logical order that they should have been.

We've all heard this multiple times over the years. "AutoNumber primary keys should not be used for anything other than identifying records."

Just as there is no inherent order among records themselves in a table, there is no inherent order in the assigned AutoNumbers used for primary keys.

The reality of data is that any logical order implied by the sequence of values in an AutoNumber is coincidental and an artifact of the way those records were added.

To take a trivial example.

I find that I biffed the values in a record after a week. Numerous other records are added during that week. I delete the botched record and readd it correctly. Now, in a standard accdb design, that replacement record has an AutoNumber a ways down the sequence. It doesn't mean anything, though. Sorting that table on a truly relevant value, like OrderDate, will put that record into the correct position for that sort order, irrespective of its AutoNumber value.

Or, perhaps, a more realistic scenario.

You hand a stack of paper orders to a data entry person to add to the Access database. Those paper orders are organized alphabetically by customer name, NOT by the date they were received. So, the order entry person enters them that way, assigning new AutoNumbers in the process to the Primary Keys. Now, if you try to sort that table by Order Date, those AutoNumbers will go all out of sequence. It doesn't matter what the sequence is for those Primary Keys. What matters is the Sort Order currently applied.

 

[jwcolby54]

No I don't think you're missing anything. I have a template database but for some reason when I append the tables they do not start at 1 again. I don't know if it's bringing in the number from the tables I'm appending, I don't know why it's not starting as 1. I don't understand it either. I've done everything that is recommended to do and it still wouldn't be set as 1. Now that I've done the reset I will see if that stays at 1 for all new appends.

Is the append query bringing in the autonumber field from wherever the data is coming from? If so then that will "override" the autonumber value. If you are not bringing in the old PK from wherever the data is coming from then... but the bigger question is:

The autonumber is "supposed to be" used as a PK. If you leave behind the PK from the source table then how are you going to hook up the data to child records of the data being imported.

I suggest that, unless the autonumber is very high and you have a real issue with that, you leave the autonumber alone and let it do it's job, being the PK. An autonumber PK is supposed to have no meaning other than "this is the PK". If you need to know the order entered or some such, create a date field called "date entered" and put an index on it.

 

[jwcolby54]

We've all heard this multiple times over the years. "AutoNumber primary keys should not be used for anything other than identifying records."

Just as there is no inherent order among records themselves in a table, there is no inherent order in the assigned AutoNumbers used for primary keys.

The reality of data is that any logical order implied by the sequence of values in an AutoNumber is coincidental and an artifact of the way those records were added.

To take a trivial example.

I find that I biffed the values in a record after a week. Numerous other records are added during that week. I delete the botched record and readd it correctly. Now, in a standard accdb design, that replacement record has an AutoNumber a ways down the sequence. It doesn't mean anything, though. Sorting that table on a truly relevant value, like OrderDate, will put that record into the correct position for that sort order, irrespective of its AutoNumber value.

Or, perhaps, a more realistic scenario.

You hand a stack of paper orders to a data entry person to add to the Access database. Those paper orders are organized alphabetically by customer name, NOT by the date they were received. So, the order entry person enters them that way, assigning new AutoNumbers in the process to the Primary Keys. Now, if you try to sort that table by Order Date, those AutoNumbers will go all out of sequence. It doesn't matter what the sequence is for those Primary Keys. What matters is the Sort Order currently applied.

> "AutoNumber primary keys should not be used for anything other than identifying records."

^THIS!!!

 

[gemma-the-husky]

We've all heard this multiple times over the years. "AutoNumber primary keys should not be used for anything other than identifying records."

Just as there is no inherent order among records themselves in a table, there is no inherent order in the assigned AutoNumbers used for primary keys.

The reality of data is that any logical order implied by the sequence of values in an AutoNumber is coincidental and an artifact of the way those records were added.

To take a trivial example.

I find that I biffed the values in a record after a week. Numerous other records are added during that week. I delete the botched record and readd it correctly. Now, in a standard accdb design, that replacement record has an AutoNumber a ways down the sequence. It doesn't mean anything, though. Sorting that table on a truly relevant value, like OrderDate, will put that record into the correct position for that sort order, irrespective of its AutoNumber value.

Or, perhaps, a more realistic scenario.

You hand a stack of paper orders to a data entry person to add to the Access database. Those paper orders are organized alphabetically by customer name, NOT by the date they were received. So, the order entry person enters them that way, assigning new AutoNumbers in the process to the Primary Keys. Now, if you try to sort that table by Order Date, those AutoNumbers will go all out of sequence. It doesn't matter what the sequence is for those Primary Keys. What matters is the Sort Order currently applied.

This bit.

Just as there is no inherent order among records themselves in a table, there is no inherent order in the assigned AutoNumbers used for primary keys.

That's not correct. The order of the autonumber IDs corresponds to the order in which records were inserted. It's not random. If you insert records to fill gaps, then that's no longer correct. That's the point I was trying to make.

 

[DHookom]

I had not known you could insert an Autonumber between two other values. For instance if you have five records with values 1-5 and delete the 3 record, I wasn’t aware you could insert a record with 3 as the autonumber. I would appreciate if some could provide a small database with a demo of how this is done.