UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Code For Generating Unique Ids Based On Given Criteria, Access 2010    
 
   
fdanos
post Aug 19 2019, 08:35 PM
Post#1



Posts: 4
Joined: 18-August 19



I am trying to design an access database for our outreach program. I am not an IT person, but I have been using the North wind template to help me.

I need help to write a code to automatically generate clients unique identity codes (UIC Code) based on the following parameters (questionnaires):

1) What were the first two letters of your last name at birth? (write first 2 letters only)
2) What is your district of birth? (write first 2 letters only)
3) Are you left-handed or right-handed? (write R for Right-Hand, L for Left-Hand)
4) What is your order of birth? (Write 01, 02, 03 etc.)
5) What is your gender? (write 1 for Male, 2 for Female, 3 for Transgender)
6) What were the last two letters of your first name at birth? (write last 2 letters only)

In my clients table, there is a field for "UIC Code" and it is the primary key of my clients table.

I tried using query to generate the UIC code and it worked, but my issue is the UIC code generated by the querry is not updated in the source table (clients table). That is why I feel that I should use a code to generate the UIC code.

Im attaching copy of the database here too



Please help me
This post has been edited by fdanos: Aug 19 2019, 09:02 PM
Attached File(s)
Attached File  KP_Database_Draft_v20Aug2019.zip ( 474.91K )Number of downloads: 4
 
Go to the top of the page
 
RJD
post Aug 19 2019, 08:47 PM
Post#2


UtterAccess VIP
Posts: 10,019
Joined: 25-October 10
From: Gulf South USA


welcome2UA.gif

QUOTE
I tried using query to generate the UIC code and it worked, but my issue is the UIC code generated by the querry is not updated in the source table (clients table). That is why I feel that I should use a code to generate the UIC code.

Hi: Have to tried turning the SELECT query into an update query? This assumes you have an appropriate field in your table to hold the UIC.

However, if you can always reliably generate the UIC, there may be no reason to store the value.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
theDBguy
post Aug 19 2019, 09:01 PM
Post#3


Access Wiki and Forums Moderator
Posts: 76,315
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UA! welcome2UA.gif

You simply need to store the answers to those questions in six separate fields. It's very easy to combine them together to form your UIC. There's no need to store the UIC itself.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
fdanos
post Aug 19 2019, 09:04 PM
Post#4



Posts: 4
Joined: 18-August 19



Thanks,

I will try that
Go to the top of the page
 
fdanos
post Aug 19 2019, 09:06 PM
Post#5



Posts: 4
Joined: 18-August 19



I did. I have all six fields in the table. But I still need the UIC code itself because I want to use the UIC code as the primary key rather than the auto number field.
Go to the top of the page
 
missinglinq
post Aug 19 2019, 09:09 PM
Post#6



Posts: 4,634
Joined: 11-November 02



This is really just a calculated Field...and as stated doesn't need to be/shouldn't be stored...simply re-calculated as needed.

More importantly, though, you should be aware that there is no way to assure that this will be a unique ID number! I've seen systems like this used n the past...and duplicates are often created. To be really unique you have to have a part of the Field that is truly unique!

Linq ;0)>

--------------------
Hope this helps!

The problem with making anything foolproof...is that fools are so darn ingenious!

All posts/responses based on Access 2003/2007
Go to the top of the page
 
projecttoday
post Aug 19 2019, 09:17 PM
Post#7


UtterAccess VIP
Posts: 10,966
Joined: 10-February 04
From: South Charleston, WV


What am I missing? So you have two never-married, right-handed women who were only-children from the same town: Maureen Jones and Mary Johnson.

--------------------
Robert Crouser
Go to the top of the page
 
fdanos
post Aug 19 2019, 09:25 PM
Post#8



Posts: 4
Joined: 18-August 19



Thank you guys.

Its clear now that the UIC code is not the suitable field for PK. Also, it will be auto generated/calculated and there is possibility of duplication.

I will use client ID (autonumber) for PK instead
Go to the top of the page
 
dale.fye
post Aug 20 2019, 06:54 AM
Post#9



Posts: 160
Joined: 28-March 18
From: Virginia


@fdanos

Ding! Ding! Ding!

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
nvogel
post Aug 20 2019, 07:30 AM
Post#10



Posts: 998
Joined: 26-January 14
From: London, UK


QUOTE
I will use client ID (autonumber) for PK instead


Think about how people will really be identified in practice. Unless everyone is issued with their client ID number and can remember it then it may be of limited use as an identifier. My guess is that your UIC was intended to be a more user-friendly alternative to an arbitrary number. I don't think it was a good design the way you proposed it but autonumber isn't so good either. Simple incrementing numbers without structure, check digits or other semantics tend not to make good identifiers for people to use.

This post has been edited by nvogel: Aug 20 2019, 07:31 AM
Go to the top of the page
 
projecttoday
post Aug 20 2019, 08:37 AM
Post#11


UtterAccess VIP
Posts: 10,966
Joined: 10-February 04
From: South Charleston, WV


How did you come up with that? You would have to ask people if they're right-handed or left-handed and I don't recall ever being asked that, not even as a security question. Someone trying to hack in would only have to make 2 attempts to get that right.

--------------------
Robert Crouser
Go to the top of the page
 
kfield7
post Aug 20 2019, 08:40 AM
Post#12



Posts: 992
Joined: 12-November 03
From: Iowa Lot


I suggest using an autonumber PK internal to the database to identify the record, but allow a user to choose their own UIC, submit it, and if duplicate, add a digit(s) (doesn't Yahoo do that?).
then the memory demands on the user are of his/her own choosing, and the database doesn't care.
Beats having to remember 6 parts, and the order of the parts.

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th September 2019 - 12:34 PM