AutoNumber In A Make Table Query

[DataChick]

I need to develop a make table query that will assign a number to each record in numeric order.

Is there a way to do that?

(I apologize if there is a FAQ for this, but the search function is down and I need this ASAP.)

Thanks in advance for your help.



"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[PHV]

Use an append query instead.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DataChick]

I have tried that when I delete the data and re-append the new data, it doesn't start at 1 again and I need it to start at one every time.

Do you know how to reset the autonumber to always start with 1?

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[lespaul]

I believe to get a starting number of 1 each time, you have to recreate the table each time.

Autonumber is a running count. If you add 1000 records to a blank table, you will have numbers 1 - 1000. If you find that you made a mistake and delete records 501 - 1000, the next number will be 1001.

Leslie

 

[Golom]

You can reset an autonumber by deleting all the data in the table and then compressing the database. An alternative is to DROP and recreate the table before you append to it.

 

[dhookom]

The only way to reset your autonumber is to delete the records and then compact the database.

Do you have a good reason to start over with the numbering? Most seasoned (old) Access programmers don't care what the autonumber value is. It is only import that the value is unique.

One workaround in a maketable query is to create a single autonumber field table with one record. Include this table in your maketable query.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[RoyVidar]

Compact repair should do that, else you could try:

[tt]dim strsql as string
strsql="alter table YourTable alter column YourAuto int identity (1,1)"
currentproject.connection.execute strsql[/tt]

- ADO for version 2000+, NOTE - assigning a meaning to an Autonumber means you're probably using an autonumber for something it's not intended too

Roy-Vidar

 

[PHV]

Instead of deleting the data, delete and recreate the table.
Or do a maketable query and then:
DoCmd.RunSQL "ALTER TABLE myNewTable ADD COLUMN myID COUNTER(1,1)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DataChick]

The reason I need to reset the autonumber (or perform the autonumber in the query) is that each week for this report I have to rank the results and they shift every week.

I tried creating the single autonumber field with one record and adding it to my maketable query but they all had the number 1.

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[dhookom]

I am surprised it duplicated the autonumber. I tried again after deleting the record from the autonumber table. I ran the make table and then ran a similar query only after removing the autonumber table and changing the query to an append query.

Running the make table and then the append query correctly began the autonumber with 1 and increasing.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Golom]

If you just want the AutoNumbers to represent Rank then use a query and adjust the value displayed like this

Select 
tbl.ANumField + 1 - (Select MIN(ANumField) From tbl) As Rank,
Fld2, Fld3, etc...
From tbl
Order by 1

That just starts the "Rank" field at 1 regardless of the actual values in the AutoNumber field.