How do i create a "unique" field (1 Viewer)

[unclefink]

I am trying to figure some things out and thought of something that may solve my problem.

I have a table as followed;

EmployeeID: PK
FirstName
MInitial
LastName
Company:FK
Department:FK

What I want to do is create a new field that automatically populates based on what is entered into the above fields.

Essentially creating a single field with unique data generated by multiple fields.

For Example if I entered the following informaiton:
EmployeeID: 1 (AutoNumber)
FirstName: John
MInitial: P
LastName: Doe
Company: FederalGov
Department:Test

The new field can be generated as followed: JohnPDoeFederalgovTest

What i plan on doing with this is making this "generated" field an index so no new duplicated records can be added.

Any help would be greatly appreciated as well as other reccomendations so as not to allow duplicated data.

Respectfully,

 

[John Big Booty]

You already have an AutoNumber Primary Key field, you don't need another "Unique Field". This would essentially be a calculated field, and as such storing it would not be in the interests of good Data Normalisation practices.

You can use the DLookUp() function in the Form's Before Update event to check and warn of potential duplicates.

 

[unclefink]

John,

I think you were involved in my initial posting that this was referenced in. I am relatively inexperienced with writing vba which i believe is causing a lot of my own confusion.

I had this same issue in another database I built that referenced First Name Last Name and date of birth. I tried to use that code for this application after adding the necessary changes to no luck. I suspect the difference in the last database to this new one is the fact I was searching for "raw data" however in this case i am searching for a foreign key as part of the "dlookup".

Here is the code I am using in the last database; however i'm having no luck making necessary changes to apply it to the one i'm working on now.

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If DCount("*", "TableNameHere", "[fname]=" & Chr(34) & Me!fname & Chr(34) & _
   " And [lname]=" & Chr(34) & Me!lname & Chr(34) & _
   " And [dob]=#" & Me!dob & "#") > 0 Then
     Msgbox "Person already exists."
     Cancel = True
   End If
End Sub

Can you tell me what I need to change to accomodate this new lookup.

I want it to check tblemployee
FirstName
MInitial
LastName
Company- which will be a FK reference.

Also the "MInitial" will not always have data in it; therefore Null values are acceptable.

Thank you in advance for any help you might give.

respectfully,

 

[gemma-the-husky]

unclefink

if you want to use the employee as a FK in another table - then i would add a autonumber key to the employee table, and use that for the FK.

the matter of controlling the validness of the employees in the employee is a different issue, and best managed by unqiue keys in the employee table.

 

[Pat Hartman]

There is no reason to store the concatenated string as a unique index. You can simply create a unique index that is comprised of multiple columns. Access allows a max of 10 columns per PK or Unique Index. Other RDBMS' allow more.

With the table in design view, click on the indexes icon on the ribbon (or toolbar). To add a new index go to the first completely blank row. In the first column add a name for the index. In the second column, select the first field. At this time set the unique property at the bottom of the form. To add a second field, go to the next available line. Skip the name column and add the next field in the second column. On the next row, add the third index field, etc. By leaving the index name blank, you are telling Access that all the columns belong to the last named index.

 

[qaziashfaq]

thanks your sharing information.