Combine two fields into another field within SAME table

[ivchisholm]

I have a table [Consolidated] which lists employees. The SSN used to be used as the primary key (it was legal, given it was a government database), but now I want to delete that field and replace it with a new primary key. I have fields [LName] [FName] [MI] and [Last4] among several others. [Last4] refers to the last 4 digits of their SSN.

I want to create a new field in the SAME table [Consolidated] that combines the [LName] and [Last4] fields, creating a unique identifier that is easy to remember. Example: [LName] = "Smythe" and [Last4] = "3434". The new [EmpID] would generate "Smythe3434" or maybe "SMYT3434" (First 4 letters of last name and Last4).

Any advice on how to have [EmpID] automatically generate upon filling in [LName] and [Last4]?

I want to do this because:

Using only the [Last4] would generate duplications after 9,999 people. My database won't ever have that many people, so this is unlikely to happen, but possible.

Obviously, I don't want to use only [LName], as I already have four pairs of individuals with the same last name.

But two people having exactly the same last name and the same last 4 of SSN is HIGHLY unlikely. Using this format is something that comes naturally to employees, as many of the websites they access requires a similar format for their userid. But I have never done this within this database. It is time for it to be done.

[NeoPa]

It would need to be done within a Form of course, but the setup would be something like :

Use AfterUpdate event procedures on all the controls that maintain the fields that you want to be used to make up your PK. Each event procedure would call a separate procedure that builds up the composite value from the subordinate parts and checks that there is a valid result. The record should not be allowed to be saved when the status is not valid. Otherwise, the control used to represent the new PK field should be updated to the valid result.

[Stewart Ross]

Although it is easy enough to concatenate two fields to do what you ask I would be sceptical about the result really being unique. A primary key must in all circumstances be unique. I doubt very much that the combination you describe is anything like unique - the last four digits of the SSN will be duplicated by chance in at least 1 in 10,000 records. If this duplication occurs for records of persons with common names such as Smith, Jones and so on the combination is not likely to be a true candidate key.

Anyhow, in the query designer you can enter something like this to create the concatenated result:

NotReallyACandidateKey: [LName] & [Last4]

Or if you want to restrict the name to just four characters (in which case it will be a certainty that you will face duplicates sooner or later) you could use:

EvenWorseNotACandidateKey: UCase(Left([LName], 4)) & [Last4]

As you may gather from what I've said above, I really think you need to reconsider what constitutes a candidate key in this case.

-Stewart

PS I had not seen your post # 3 (or NeoPa's response Post # 4) when I wrote this. You clearly recognise that the components of your candidate key are not unique. I disagree iwth you about the chances of occurrence of a non-unique key being consequently low - you can bet it will happen sometime, and what then? You will not be able to store the record concerned.

[ivchisholm]

My database, at any given time, contains data on about 100 current employees. I also maintain the data on past employees for a period of two years, so about another 150 to 200. After 2 years, they are deleted.

Thus, there will be a maximum of about 300 employees' data contained within the database. The chances of duplicated last 4 digits of SSN, as you stated, Stewart, is 1:10,000 and I don't know the "chances" of duplicated last name. I know that I have had duplicate last names before...I currently have 4 pairs of exact same last names, and we are about to have a 5th pair. But what are the chances of two "Jones", "Smith", "Brown", etc, sharing the same last 4? I would venture to say at LEAST 1:40,000 (I bet it would be MUCH higher, like 1:100,000 or more).

Assuming those chances were exactly 1:40,000 and my database contains 300 records, I would have to use the same pair of names 133 times before I tempted fate enough for the pair to have the same last 4. Assuming that once every 5 years we got a new person in with the last name "Smith" (in reality, it hasn't been as common as one might think), it would take well over 500 years of getting a new "Smith" before I chanced getting two people named "Smith" with the same last 4. Before that happens, we will have another database mandated for use (it's already in development). This is just a database that will tie us over for the next 5 years, maybe up to a decade, while they complete the database, test it out, mandate its use, and different agencies across the board actually put it to use.

I do agree that, put to use long enough, or if I was maintaining a database with thousands of records instead of only a few hundred, the chances of occurence would be much higher. But (and I didn't state it in my original post), there really aren't a whole lot of records.

Maybe to lessen the chance of duplication even further, I will include last name, 1st 3 characters of first name, and last 4. That should raise the chances of duplication to at least 1:300,000, which is much larger than the entire agency worldwide.

I would NOT use this format on a larger database. I don't even like using it in this instance. But the people USING this database insist on an easy setup like this. They are extremely database illiterate...and that is pretty bad coming from me. I would consider myself a "fairly experienced novice". Been working with Access for about 7 years, but mostly simpler designs. Over the last year, I've been getting into more and more complicated design. But for me to call them DB illiterate, I mean they haven't designed anything more than a spreadsheet. When you explain anything DB to them, they drool!

Your thoughts, Stewart?

[NeoPa]

In the OP it mentions employees, but it shortly afterwards mentions that it's a government database which leads one to think in terms of the whole population. I suspect (and Stewart can correct me if I've misfired) that he was thinking more along those lines when he offered his good advice on preparing for uniqueness. It's certainly something we find many members haven't considered adequately.

In this case, and particularly in view of your fuller description of your situation, it seems like you have given more than adequate consideration to the issue and have covered all your requirements appropriately.

[ivchisholm]

Yes I said government to indicate that it was for a government agency. Private company computer applications are required to fit within certain rules (especially when dealing with privacy act data), while some government applications are allowed certain leeway if protective measures are used.

So I made sure to indicate government, as I have asked this question on other boards, and people answered "you're not supposed to use SSN" without answering my question.

I have created the query that combines the last name and last 4, and it work well (thank you, Stewart). What I cannot figure out is how to then apply the new concatenated data from the query onto the form, thus inputting it into the table's PK field.

[NeoPa]

This question was essentially double-posted as AfterUpdate to populate field after data entry into two other fields. This is not allowed and the other (newer) thread has now been locked to further posting. Members involved in both threads can continue their discussions here, in one place.

[patjones]

I'm going to respond in this thread since it's what the other experts have been responding to.

As I mentioned previously, when the concatenated value appears in the third text box, you need to check in the table for that value using, for instance, the DLookup( ) function. If DLookup( ) returns null, then the value is not in the table and you need to add it. For instance:

DoCmd.RunSQL "INSERT INTO Consolidated(LName, Last4, EmpID) VALUES ('" & Me.txtLastName & "','" & Me.txtLast4 & "','" & Me.txtEmpID & "'")

Note that I made up the names of your text boxes: txtLastName, txtLast4, txtEmpID. You have to change those to whatever you have them named as.

[NeoPa]

I've been somewhat busy tidying things up, but now I'm finished I'd like to outline an approach for you that should cover everything neatly. This is not a very straightforward situation, so needs some careful thinking :

1. Change the design of your table to include an extra field that will (later) be used as the PK. For now it is a simple, unindexed, field (I'll refer to it in here as [NewPK]).
2. Design a form that implements the requirement to create the value of this new field in the bound control on the form from two other controls (Post #2 details this for you).
3. Run an UPDATE query to ensure all existing records in the table have PK data in the new field :
   

   UPDATE [Consolidated]
   SET    [NewPK] = UCase(Left([LName], 4) & Right([SSN], 4))

4. Enable the new field as the PK.

[OldBirdman]

You underestimate the chances that you will have duplicates. True that if the last names match, the chances that the Last4 will also match is 1:10000. However the 3rd identical last name may match either the first or the second Last4 with a probability of 2:10000. If the 4th match has a probability of 3:10000 of matching one of the first 3, then for four identical last names, the probability of a match is 6:10000.
This probability is still small, but not impossible. If it should occur, your database may fail, or divulge privileged information that could get to the wrong person.
I, personally, would not take this chance.

[patjones]

Why not just use an encrypted version of the full SSN?

[NeoPa]

See post #4 for that Pat. Near the bottom.

[ivchisholm]

ok, Didn't see what NeoPa said, so going to try it out. I'll update with progress soon.

[NeoPa]

Understandable. It was a busy period for posting it seems. We'll catch your response later.

[patjones]

I work for a large NYC government agency and got curious about this last name/last 4 situation. I just did a DB2 run and found that out of 8,681 active employees - we have eight sets of duplicate last name/last 4 combinations (two occurrences in each set)...about a 0.2% occurrence rate.