Adding autonumber to a table (1 Viewer)

I have two tables, which are joined by a union query. This is then appended to another an other table, called joined_data_tbl. Before appending the data from the union query, I run a delete query to delete all the records from joined_data_tbl, this is because some of the data in the original two tables may have been modified and some more records will have been added.
When I add an autonumber field to joined_data_tbl I get all the records numbered, but the problem is, when I run the delete and append queries the auto numbering continues on from where the last record was before deleting.
How do I stop this and make the auto number start from 1 each time I run the delete and then append queries. :banghead:

How do I stop this and make the auto number start from 1 each time I run the delete and then append queries.

Click to expand...

you don't, or at least it should not be necessary, the autonumber is just a means of identifying a specific record and has no other meaning at all. It cannot be relied on to be consecutive, indicate the order in which records were added and should not be assigned any meaning such as an invoice number.

You cannot prevent the behaviour, but you can take action to reset it - see this link

https://support.microsoft.com/en-us/help/812718/how-to-reset-an-autonumber-field-value-in-access

UNION, delete, append, adding autonumbers? Why such a rube goldbergian database?

How do your two initial tables get populated? Why are they seperate if the data ultimate gets put into one table?

I agree its seems a very convoluted way about things, but as I'm still a novice to Access it seems to work for me.
table1 is the result of an imported Excel file and table2 is one where records can be added and mended. They are then brought together to give a complete history of all train wheels used across the fleet.
The auto number is used in joined table3 so I can select a specific wheel from any train in the fleet, using the highest auto number for a particular wheel position in a train.
The way I have it works, just that every time I update the joined table3 the auto number keeps continuing on, causing the auto number to get very high very quickly, when there are 15,000 records in table3.
I think I will have to rethink my approach to this problem.
Thanks

what is in the excel file? new records or amendments to existing records? or both?

If the former, you should just be able to append these records to the 'primary' table

If it also contains amendments to existing records you can use an update query

Sounds like your Access app is not the data store of record for any of the data. The data is being managed by other applications. Your database I presume is being used simply for reporting.

I created a few apps like this for some analysts. Reminding them to delete the data and compact was a bridge too far so I created a template database with empty tables all set up as I needed them to be. When they initiated the import process, I deleted the old copy of the temporary BE and replaced it with a copy of the template. This completely eliminates the bloating issue and it also has the side effect of resetting the autonumbers. All the Templates were stored in a single folder and included "Template_" at the beginning of their name. As part of the copy process, the "Template_" prefix is removed. Here's the copy code. Modify the hard coded paths to be your own or make them variable. Then call it using:

Call CopyTemplate("LetterManager_BE.accdb")

Replace the file name argument with the name of your temporary BE.

Thanks Pat for your reply, I'm an absolute novice with code, but will attempt to use your code.

CJ_London, the Excel file contains new and amended to existing records. As I say I'm still learning Access, would an update table allow addition of new records and the modification of existing records?

using the highest auto number for a particular wheel position in a train.

Click to expand...

You are new so have yet to learn the ins and outs of autonumbers. However, this is an incorrect use - in the sense that it means something relevant to the real world. The autonumber field is intended strictly and only as an internal number that could be written in Roman numerals for all anyone cares. Its purpose is to allow Access to establish a correlation between tables of related data when no natural prime key is available.

To allow it to have meaning with respect to your real-world model isn't normally part of the deal. The MOST that I have ever done with an autonumber is to provide it as a "confirmation" number - and to be rigorous about it, even that is stretching its intended use. To have it take on positional meaning in your model indicates a design issue. Not earth-shaking, please understand. Just that if you wanted to indicate a position, you should have a position field.

The technical reason is that the field is doing two things at once. It is used as a PK for a table AND it is used as a position indicator in some real-world thing. BUT that real-world thing (from your description) is part of a larger assembly - the whole train? - and so an autonumber implies one assembly and the next higher autonumber implies a different assembly - but the next one after that does NOT.

If you have not yet studied database normalization then may I respectfully suggest that you do so? You can search this forum for "normalization" and get a ton of hits, or you can search the web for "database normalization" and get upwards of a gazillion hits. (Be sure to qualify web searches, since "normalization" applies to mathematics and diplomacy as well.)

Thank you for taking the time to reply. My database did appear to be working how I wanted, but someone came up with a suggestion to improve data input that has meant the need for the joining of the two tables into one.
For the time being I have used the following code:
Function Resetno()
CurrentDb.Execute "ALTER TABLE data_complete_tbl ALTER COLUMN IDnumber COUNTER(1,1)"
End Function

I understand from your post that I should not be relying on the auto number but for the time being this method works.
I fear I will have to go back to the beginning to look how the tables are set up.
Thanks again.

Doc is 100% correct in saying that you should not depend on autonumbers. Since you are rebuilding this database every time you need to import fresh data, how would a user ever know what the autonumber value was for any given record? I think the need for them to always start from one is simply in your mind.

Take a look at the code I posted. It is an easy way to avoid bloating when your data comes from another application and you can't link to it so you have to download it every time you need to refresh it.

Pat thanks for the reply. I have looked at your code and to be honest it’s pretty much witchcraft to me. It seems I am still grappling with the basics of dB design!

But can't Autonumbers reliably be used to find the latest record added to the table? Since Autonumber does not fill in gaps caused by deleted records, I should always be able to assume that the highest Autonumber is the most recently added, right?

Or what happens if I reach the highest Autonumber (2 billion something)? Will it then go back and use the numbers that have been deleted?

Technically, no, practically--perhaps.

You are using an APPEND query, therefore all those records are being added simutaneously. They have to get a unique autonumber so one of them will have the highest value. That may or may not matter for your specific use, but from a technical sense you cannot accurately determine which went in last--it was tie among all those records.

Also, and what we see a lot, is the step beyond that--thinking that you can use the autonumber to determine the 'latest record'. Note that I omitted the word 'added'.

Suppose you wanted to determine the latest sales figures for people--but you forgot to add Toms December total. You've already added everyone's January totals, so today you add his December total to make it right. If you were to solely rely on autonumbers his latest sales totals would pull the December record (because it was the last added) and not January's.

That's why its best if timing and order are important you add a date/time field to your database to capture it, then use it to to determine order on your data.

Kroniz - you should start your own thread if you have questions. Don't hijack other people's threads.

But can't Autonumbers reliably be used to find the latest record added to the table?

Click to expand...

Yes, they can be used to find the newest record. However, in a multi-user environment, that might NOT be the record YOU just added. Between the time you add a record and then look for the autonumber, someone else could have added a record.