Change starting autonumber in a database

[SGNTGH]

Hi,

I am in the midst of a research project where 2 separate sites are collecting data. Our initial site has been collecting for a while and our primary key (which links through a number of tables on a one to many basis) is Patient ID no. This is an autonumber field set by the main table and obviously generates a new autonumber for each ID as we go.

The second site need to use a duplicate copy of the original database but obviously we don't want the ID numbers to be duplicated. I just want to therefore change the primary autonumber field so that instead of starting at 1 it starts at 500. I have tried the microsoft support guidance but got so far and realised the instructions wouldn't do what i thought they were going to.

Any ideas? Seems like it would be a simple thing to do but never is with Access!

Thanks,

Sarah

[pbaldy]

Create an append query that appends a record with 499 in the autonumber field. After you run it, 500 will be the next record created. You can delete the seed record after they start using it.

[CJ_London]

The only purpose of a primary key is to uniquely identify a record. By starting autonumbers at different points you are giving it a meaning - the site is identified depending on the key value. might be better to include a field to indicate the site, then set the autonumber field to random rather than increment. And what happens if the first site has more than 500 records?

[SGNTGH]

Thanks both. The first site will not exceed 500 - we will stop recruiting well before we reach that number. We don't want any duplicate numbers because it is for a patient based research trial where all data has to be anonymised once an ID no is assigned to a patient and we can't risk accidentally assigning data to the wrong patient by having 2 databases with the same numbers, even if there is a field to identify the site.

Append query sounds promising, thanks, but I am a serious beginner when it comes to Access! Will see if I can figure out how to do this query with my Access for dummies book...

[Micron]

If your pc's are fairly robust and having unique id's is of VITAL importance (it seems it is) then you ought to consider the use of GUID data type as the primary key for patient. I don't know what the chances of duplication is (probably around 0.00000000000001%), but it's far better than your suggestion from that perspective. This is just an example to illustrate my point, but if you can monkey with the autonumber sequence, so can anyone else down the road. If that happens, will you duplicate? Who knows?

The GUID data type is a large number, but it's the way it is built that pretty much guarantees uniqueness. Part of the value is clock based, part comes from the identification of the hard drive (serial number? I forget exactly). What is the chance that 2 hard drives that have the same contributing id doing something at the exact same clock millisecond? As if that were not enough, there are more parts to the ID number.