Solved

Generate semi random id numbers in SQL/Access

Posted on 2011-09-29
4
251 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 500 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

707 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