Improve company productivity with a Business Account.Sign Up

x
?
Solved

Generate semi random id numbers in SQL/Access

Posted on 2011-09-29
4
Medium Priority
?
266 Views
Last Modified: 2012-05-12
We are building a database to track the attendance and survey answers of participants in a program.  For the most it is imparitive that the participants of the program remain anonomous and we like to facilitate this by generating id numbers at the time of enrollment/intial data entry into the SQL database.  I am supposing that we could just use the incremental primary key number (int) in the main table but I am hoping to make the id number be in a xx-yyy-zzzzz where xx would be a country code, yyy would be a site id and the zzzzz would be for the individual.  What would be the best field type and properties in the sql table and what would be the best data entry input mask in a Access 2010 form.  Thank you for your suggestions.
0
Comment
Question by:MarkSnark1
4 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36817845
In Access you can use an autonumber field with the "New Values" property set to: Random
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 36817988
I generally don't use the Auto-Number for anything other than a unique primary key identifier.  If that value means something other than that or will be cross-referenced in other tables ... then you should establish this value yourself ... kind of like Employee ID, Customer Number, Check Number fields.  That's a whole different discussion!!!

To answer your question ... depends on your skill level and how fancy you want to make it.

I use a 1 record table in my apps called "SetupGeneral" to store values (Long Integer or Double) such as Next_WorkOrder, Next_Check_Num, Next_Vendor_Num, etc., etc.  The use VBA code to select and increment it when needed.

In your case, when creating a new attendee, you could use drop down boxes to select country code and site id.  Then concatenate them together with the Next_Attendee_Num in the SetupGeneral table to form a Text Attendee_ID.

All you will do is first look up the Next_Attendee_ID in your table, store it to a variable the increment it by 1.

I can provide some sample code if you are interested in this method.

ET





0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft Access MVP) earned 2000 total points
ID: 36818013
If ... you want to generate random results ... here is an example:

SELECT TOP 10 tblEmp.EmpID, tblEmp.EmpName
FROM tblEmp
ORDER BY Rnd(Int(Now()*[tblEmp].[EmpID])-Now()*[tblEmp].[EmpID]);

In this case, EmpID is the Access Autonumber.  So, this gives 10 truly random Employee Names EACH time it's run, even if you close and reopen the database.

Here is a slightly different version:

SELECT TOP 1 tblEmp.EmpID, tblEmp.EmpName
FROM tblEmp
ORDER BY Rnd(Replace(TimeValue(Now()),":","")*[tblEmp].[EmpID]-Now()*[tblEmp].[EmpID]);


mx
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36897426
With SQL Server 2008 you can define your column as a UNIQUEIDENTIFIER, this will produce a GUID.

The problem with random values is that they are lousy candidates for clustered indexes.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question