?
Solved

Generate semi random id numbers in SQL/Access

Posted on 2011-09-29
4
Medium Priority
?
254 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 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 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