Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Generate semi random id numbers in SQL/Access

Posted on 2011-09-29
4
Medium Priority
?
259 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 MVP, Access and Data Platform) 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

810 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