Solved

Generate semi random id numbers in SQL/Access

Posted on 2011-09-29
4
245 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 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

713 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