Continuation of Autonumber problem? UPDATED

[dandyboy]

I wish to say, Thanks, to all those who responded to:
I had this huge table which I divided up into 4 smaller tables to make this more manageable. However, the autonumber field, which was the primary key in the old table, still has the same row numbers as it did in the huge table. I played with it awhile to see if I could get that field to start at one, but I couldn't.
I tried to insert another field in design view, I made this my Primary key and turned it on/off(this made no difference) and then define it as autonumber but I got message boxes popping out saying that ACCESS won't allow that. I want to hear back from BETSVIGI9, who has direct experience with this. I need to be walked thru this for I am very new to MicroSoft stuff and I've had classes in this stuff, but I'm unsure of exactly what is meant'join'?

 

[SgtJarrow]

dandyboy,

If I understand you correctly, you have a table with an autonumber field that you want to resequence staring from 1 again...in other words:

Have want
1 some data 1 some data
2 some data 2 some data
5 some data 3 some data
14 some data 4 some data
24 some data 5 some data
25 some data 6 some data


If this is correct the process, is fairly easy....

1. In your database window, right-click on your table in question and select copy.
2. Right-click in some white space and select paste.
3. Choose to copy data structure only. Name it whatever you want.
4. Create a query from the old table containing all of the fields except the autonumber field that is out of order.
5. Right-click in the top portion of the query window and select query type -> append and choose the name of the table you chose above during the copy.
6. Run the query.
7. Delete the old table and rename new one you created to the old name.

This works because Access appends the records without the autonumber and uses the new autonumber field instead. Please let me know if you have any problems. It's not important that someone else can do in one step what it took you ten to do...the important thing is that you found a solution.

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer