Random number wanted between 1000 and 9999 no dups.

[Dublinphil]

How can I generate a random positive number of 5 digits. It will be a client number, the primary key and I don't want duplicates.

 

[jjonesal]

Try adding this to the default property of the client number field in your table:

Int((9999-1000+1)*Rnd()+1000)

If you wanted to do this in a query or form, it would be the same calculation, just implemented a little differently.

Hope this helps... good luck!

 

[MichaelRed]

A "Better" soloution would be to just have a autonumber field. If you "need" to display it in your format, that is easily accomplished without the overhead and considreration of needing to keep track of how many customred / records ...


MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[jjonesal]

If the number can be no larger than 9999 and must be randomly generated, I don't think an autonumber field will work. Since autonumbers will increment sequentially, they couldn't be randomly generated.

Michaelred, If they can... Please let me know as I have some applications that I could use this with.

Also, relating to my previous post - I forgot to mention, if you don't want duplicates, you'll need to create a key or unique index on the field in question. (Or have your code perform the calculation, do a lookup in the table to see if it exists, and if so, generate a different number).

jj

 

[MichaelRed]

jjonesal,

Of course they won't be random. Why do they NEED to be randomized? What is Dublinphil going to do when -over the course of time- customers come and go and his 9K Ids are all 'use'? I "guess" if it is Cast into concrete with Bronze plaques, to be a random number beteween X and Y, SOMEbody will "DO IT". And -IMHO- leave the legacy of poor design and complete lack of forethought for someone ELSE to clean up.

I can only HOPE that I get the opportunity for that "Job" when it becomes available!



MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Dublinphil]

Why random you ask. Truth be told the clients are actually surgery patients at a hospital. The associated data will be review by many hospital and doctor types, uncluding the operating surgeon. Due to the extreem confidentiallity of the data, success, failure, death, loss of sexual function, etc. The idenity of the patient needs to be protected. Medical record number, ss#, are too traceable. Statistical reports will contain the random clientID only. When audited, results can be verifyed without exposing confidential information about the patient.

 

[andrewvanbeck]

This is really tricky if you wan't restrict traceability of your records.

You basically need a way of finding out if your new random number has been used, there are a couple of ways you can do this:

o Check your existing table to see if your new number is in there, if it is then get a new number, this is very sloppy though and could slow right down when more numbers are used because it would have to keep generating numbers and checking them.

o Have a table containing all your numbers 1000-9999, then pick one at random and use this, then delete it from the table so it can't be used again. I don't know how you would select the number at random, maybe have a little code that loads up the table, moves forward throught the records a random number of times (going back to the start when getting to the end), then grabs the number it lands on, sorta like roulette!.

If I was you I would try the second option, that way you could easily add new number if you had to, and you could see how many numbers you had left.

If you can't figure it out, email me and I will send an example:

HTH

andrew.vanbeck@semefab.co.uk

 

[MichaelRed]

Still, you are looking at a non-soloution. If the Id is known and it is part of the record, all of the information in the record is available to anyone able to access the db. The only real soloution is to use db security to restrict the access to the database. If you REALLY need to protect the information, you could also encrypt it. Between these two, you will have as much "protection" as is available. Your random number schema will not even slow down an amatur hacker. Once they have access to the table it is all over. Whatever info you have in the table is fully and immediately available. If there are related tables, the keys are -by the def of the process- available, so it is only a brief pause to look at te structure of the keys before proceeding to the related tables. If you want it to be a little less obvious, try using the GUID as the PatientID. It is -in reality- just a complicated AutoNumber, but it does NOT exhibit the sequentialness of one. And again, rember thaat anyone with access to the db can look at the entire record so the randomness of the key is meaningless.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over