autonumber

[Simon]

I run two clinics and have recently merged databases. Patients are listed in
a table "patients" and their treatments in another "treatments" the
relationship is one-2-many. The ID number is unique to each patient and has
to be manually entered each time a new patient is added.
I have tried adding an autonumber field below the ID in table design but
although this generates autonumbers for future patients it mixes up the
numbers from the previous ID field. Is there any way to insert an autonumber
field that will correspond to the original ID field and then insert an
incremental number for each new patient added?

 

[David F Cox]

You can use the generate your own sequence number. One way is to use the
DMAX function to find the current highest number in the table and add one to
it. A google groups search on "custom autonumber" will produce lots of
results.

 

[Arvin Meyer [MVP]]

You can build a new empty table with an autonumber ID field, then append all
the data from the other tables. Be aware that if the autonumber is set as a
unique index (or primary key), you will not be able to append 2 records with
the same value.

What I usually do when renumbering due to the merging of data, is to leave
all the data as is in the original tables, build a new table and add a new
autonumber field and keep the old one as a long integer field. Then I import
all the records from the first main table, and do the same for the many side
(adding a new foreign key field). I join on the old field and update the
many side with the new autonumber key from the one side. After I've done one
set of tables, I do the second database the same way.