members' IDs substitution 1

[irinnew]

Hi experts!
I have a large file of 10,000 records for patients whose ID is alphanumeric field of 9 digits (look like numbers for ex “123456789”).

I am going to take a biostatistics course on-line based on my material(typical dataset) and I do not want to provide somebody with a sensitive information (which includes patients’ ID).

On the other hand, I cannot substitute all ids with auto-sequence because the nayure of file is surveys and therefore I have multiple records for the same patient conducted in different time frame. I wonder … is there any simple way to substitute ID’s or part of ID’s ? How can I do it?

Could you please help me with it?

Thank you in advance,

Irin

 

[dhookom]

Could you provide some sample records and your desired outcome so we have a better understanding of your table structure? Do you need to change Patient IDs in just one table or more than one?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[irinnew]

I just need to code IDs. In order they stop be “real”
For example:
For digit 0-> put 1
For digit 1-> put 2
For digit 2-> put 3
For digit 3-> put 4
For digit 4-> put 5
For digit 5-> put 6
For digit 6-> put 7
For digit 7-> put 8
For digit 8-> put 9
For digit 9-> put 0

sample:
For ex. ID="123456789" should be converted into "234567890"

I handle with the only table.

How can I do it?

Thank you!
Irin

 

[Golom]

I guess this UDF would do it

Public Function IDSwitch(ByRef ID As String) As String
Dim c As String
Dim NewChar As String
Dim n As Integer
For n = 1 to Len(ID)
   c = Mid(ID,n,1)
   If IsNumeric(c) Then
      If Val(c) + 1 > 9 then
         NewChar = "0"
      Else
         NewChar = Trim(Str(Val(c) + 1))
      Endif
   Else
      NewChar = c
   End If
   IDSwitch = IDSwitch & NewChar
Next
Exit Function

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[irinnew]

Golom,

Thank you very much for your response. I created a module1 but not having any experience with Modules I wonder how to make my module connected to my table. In other words…I have the table and I have Module1 with a function in need …now how can I make both work together?

Sorry for the naïve question. Thank you very much in advance!

Irin

 

[lespaul]

call it from the SQL statement like this:

SELECT IDSwitch(YourIDField) FROM TableName

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[irinnew]

lespaul,

Thank you for your response.
I created a new quiery with the SELECT statement but failed....
I did not create a new field however. So I wonder if (YourIDField) means my new ID field or it supposed to overlap my old one?

Thank you in advance!

Irina

 

[Golom]

How did it fail? (i.e. what error message did you get?)

"YourIDField" is the current ID. The Function returns a new one based on it.

Oh ... and I just noticed Exit Function should be End Function

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[irinnew]

I tried End Function. It does not generate error message. However when I query the output is just one column with a heading Expr1000 under which all rows are empty. I tried to open original table …column values are not changed

 

[Golom]

Seems to run OK for me. I have a table like this
[tt]
Values
0123456789
5432176901
[/tt]
I ran

Select Values, IDSwitch(Values) As [NewValue] From myTable

And I got
[tt]
Values NewValue
0123456789 1234567890
5432176901 6543287012
[/tt]
Your table isn't being updated because this is a SELECT query, not an UPDATE query.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[BoxHead]

irinnew,

I'd suggest rethinking this plan. If you're really concerned about providing an opportunity for identity theft, understand that anyone who would try to "decipher" your substituted IDs would be able to break the code pretty quickly.

On the off chance that my SSN is one of your 9-digit IDs, I'd suggest creating a replica database for your on-line work.

Create a new table with two fields [tt][newID][/tt] and [tt][oldID][/tt]. Make [tt][newID][/tt] an Autonumber, but select Random in the table design view instead of Increment.

Run an append query to add DISTINCT Patient IDs into this new table's oldID field. The randomized newIDs will automatically populate.

Run Update queries as necessary to replace the real IDs in your replica database with their randmized counterparts by bringing the new table into the query and joining the oldID field on the existing tables' ID Fields.




When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[irinnew]

BoxHead, thank you very much for your valuable insight! It never even came to my mind and You are absolutely right -whoever can do the same encoding.
Trying not to involve your SSN (even although it is not ssn but I still do not want to send them as they are) into this hypothetical trouble, I tried to follow your instructions. I do know how create a newId auto number but unfortunately I failed to make in Random. In other words I set it a primary key in the Design view and then right clicked, chose property and do not see this option. I use Access 2002….


Also, saying one should run an append query to add Distinct Patient IDs…..do you mean I should create new make table query first to create Distinct IDs based on my real ID’s?

Thank you in advance,

Irin

 

[BoxHead]

I'd suggest that you create a new table just because the steps are easier for me to explain.

To be clear, do this in a copy of your database.

And, did I mention copy?

Create that table in design view with the two fields 'newID' and 'oldId' and after you set the data type for the newID field to autonumber, the properties section at the bottom of the screen should show the table's general properties. One of those properties for the NewID field is (by default for autonumbers) Increment. Change that to Randomize.

Create a new query adding an existing table with ALL of the Patient IDs in it. Double click on the Patient ID field so it is the only field returned in the query. On the properties tab for the query, change Unique Values to Yes. View that query in Datasheet View and you should see a list of all Patient IDs.

Back in design view, change the Query Type at the top of the screen to "Append Query". A dialog box will open for you to select the table into which you want to append these IDs. Select the new table you just created.

An Append To line has been added to the query grid and on that line select the oldID field as your destination. Click the exclamation mark at the top of the screen to append the data.

Open the new table and the real patient IDs should be in the oldID field and random numbers should be in the newID field.

As long as that worked, you shouldn't need the query anymore and can close it without saving.

Now, you can run update queries on each table that has the patient ID. Include the table you want to update and the new table. Join the real ID field to the new table's oldID field. What this will do is make sure that if you replaced ID# 123456789 with 34980002.387654 in your new table, anytime you replace 123456789, it will be replaced with 34980002.387654.

Let me know if you want help with the update queries.


HTH






When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[irinnew]

HTH! It looks like it works! At first I was confused that it generate initial digit “–“ for some of them but as long as data type of each table except new stayed as text it doesn’t matter!

Thank you so much!

 

[BoxHead]

The initial digit "-" is actually a minus sign. The numbers generated are random so some are negative, some positive.



When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.