Turning current fields into autonumber

[spock1971]

Hi all.

I have been tasked with replacing an external database in house and obviously the external database has multilpe tables joined by various id fields. I'm assuming when they created the db they were assigned autonumber qualities etc to create unique numbers.

However, I can't replicate that in mine. I have the external references for existing data fine. But ho do I now create a new record with a unique number in the existing field.

I tried setting it to primary key and/or no duplicates etc but it's expecting me to enter a number.

I imagine I've got to set some kind of loop to count from one and matech it and then when it finds a number not currently in use it'll stop and use that but how to do that........

Cheers for any help.

 

[neileg]

Not exactly sure what you're asking, but I'll try and answer.

If you want to create an autonumber field but retain the existing data in that field, you can do this:
Create a new table that is the same as the old table but has no data. You can do this by copying the table and pasting the structure only.
Open the new table in design mode and change the field to autonumber. Close and save the table.
Create an append query that appends all the data from the old table to the new one. You will find that the existing numbering is accepted in the autonumber field.
Delete the old table and rename the new table to the old name (if that's what you want).
The autonumber field will now behave as you expect.

Alternatively, you can create your own sequential number using DMax(). Do a search in these forums for more info.

 

[spock1971]

First idea sounds perfect. Will give it a go. Cheers