Generating ID Numbers

[Jcarmody]

I need to generate a unique ID number based on the Social
Security number. The calculation needs to generate the
same ID number every time given the same SSN number.
I've tried to use a calculation which used various pieces
of the SSN and Date of birth, but it created duplicates.
Does anyone have any ideas?

 

[Peter D]

a very very simple encryption algorthym should do this, perhaps try
searching for an encryption routine.. www.planetsourcode.com -> choose VB.

 

[Raghu Prakash]

Hi Jcarmody,

Fields that generate numbers automatically in Access
In an Access database, you can create a field that automatically generates
a unique number when you enter a new record in a table.

To create an AutoNumber field
Open the table in Design view.
To insert the field in the table, right-click in the row below where you
want to add the field, and then click Insert Rows on the shortcut menu.
In the Field Name column, type a name for the field.
In the Data Type column, click the arrow and then click AutoNumber.
If you want the values in the field to be random instead of sequential,
click the NewValues box in the bottom part of the window, and change the
setting from Increment to Random.
Tips
Because the value in an AutoNumber field is unique for each record in a
table, an AutoNumber field is a suitable choice for a table's primary key.
You can't change the value in an AutoNumber field.
If you delete records at the end of the table that contains an incrementing
AutoNumber field, the values that existed in the AutoNumber field of the
deleted records will be reused after you compact the database. For example,
suppose the values in the AutoNumber field of a table are 1, 2, 3, and 4.
If you delete record 4, compact the database, and then add a new record,
the value of the AutoNumber field in that record will be 4.
If you delete records that are not at the end of the table, the values that
existed in the AutoNumber field of the deleted records are not reused. For
example, if the values in the AutoNumber field of your table are 1, 2, 3,
and 4, and if you delete record 2, the value of the AutoNumber field in the
deleted record is not reused - even after you have compacted the database.
For more information about AutoNumber fields, type incrementing AutoNumber
fields in the Office Assistant or on the Answer Wizard tab in the Help
window, and then click Search.

For more information about compacting an Access database, type compact a
database in the Office Assistant or on the Answer Wizard tab in the Help
window, and then click Search.


For Further Informaiton :
http://office.microsoft.com/en-us/assistance/HA010550671033.aspx

Please let me know has this helped You...
Thank you...
Raghu...
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[jcarmody]

Raghu -
I probably wasn't clear enough. I am trying to create a
customer number for an individual that would link all of
their records (present and future) together with the same
ID. I can't use their SSN number because of privacy
issues. I have their ssn number on the file though. I
thought this would be a common problem but can't seem to
locate a documented approach. Any ideas?

JC

 

[John Vinson]

Raghu -
I probably wasn't clear enough. I am trying to create a
customer number for an individual that would link all of
their records (present and future) together with the same
ID. I can't use their SSN number because of privacy
issues. I have their ssn number on the file though. I
thought this would be a common problem but can't seem to
locate a documented approach. Any ideas?

This is actually quite commonly done. One example:

http://support.microsoft.com/?id=210194

Others can be found in the Access Developer's Handbook, or several
other published Access references.

It would be nice if Access provided a user-friendly tool for creating
such a "custom counter", but up to now it hasn't; you can program it
yourself, but you need to dig a bit for instructions on how to do so!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Peter D]

JC,
i think using some basic encryption code should do what you want.
if you want the customerid number to be determined from the social security
number but at the same time dont want to use the SSN (due to privacy
issues), you could simpley grab an encryption algorthm that works like such
(btw i dont know what a SSN looks like but i assume its a long string of
numbers?);
<calling code>
SSN = 123456789
CustomerID = EncryptSSN (SSN)
<end calling code>
Sub EncryptSSN(SSN as string) as string
'replace each character one by one with another character OR number,
based on some logic,
'e.g. 1 = A or 1 = 1 + 5 'the possiblites are endless
'therefore EncryptSSN = ABCDEFGHI , if 1 = A, 2=B ,etc.
end sub

and to decrypt the customerid use the same logic above in reverse.