Two Autonumbers Needed, but how??

[duckster]

I have a Client Table which has the following Primary Key: "Control ID".

The Control ID is an autonumber (ID increases by 1 with each new client).

Now, I also need to create another field called "Client ID". The Client ID is unique for each client, but needs to be a random number, in the range of between 100,000 and 1,000,000.

I've been using the Control ID for the last few months, but now need to add the Client ID. The Controld ID is mainly for internal purposes, and the Client ID will be given to the client.

I can't seem to add this new Client ID field, as tables don't allow for two autonumbers. Is there any way around this? It needs to be an autonumber field as is needs to be random, and entered automatically.

Any help appreciated, thanks!

 

[FoFa]

Take your control number, add 100,000 to it and use that?

 

[duckster]

Thanks, yes we have thought of that but we want to keep the Control ID separate from the Client ID...and also want to make the Client ID a random number, so the numbers don't go up sequentially

 

[RV]

Your approach is incorrect.
As for any data purposed to communicate externally (such as client numbers, invoice numbers, order nummers) you should not use autonumbering.
Autonumbering is only used to uniquely identify rows.

Create your own function to generate client numbers.
Can't see any reason why these should be generated "at random".
However, if you insist, use the Rnd function.

Search the forum on generating random numbering and generating sequential numbers as both are quite popular topics

RV