Store full name as field in table.

[rmulder]

I have made a few databases in the past and I've always had a field "full name" and "first name" and "last name" that I had the user fill in for new employees. The "full name" is the key for the table as it should be unique unless in extreme rare cases lol.

I would really like it when new employees were entered in my system, if I could just have them enter a "first name" and "last name". and the full name was calculated in the format "last name", "full name" in a separate field.

I know this can be accomplished in queries it just benefits me to have that full name in a field, and not sure how everyone here feels about using that for a key???

 

[boblarson]

1. Do NOT use it for a key. Names are not unique. If nothing else, use a surrogate key (autonumber). What if you happen to get two people named John Smith working there?

2. Don't store the full name. Create a base query which has all of the fields AND one you create by concatenating. Then use IT any place that you would normally use the table, if you need the full name.

 

[rmulder]

this seems very logical, only problem is i already have a database of 100 or so employees i don't really want to recreate. but... i know there would be a way to switch to auto number as the key. or get around re-entering everything in a new table. any ideas?

 

[Galaxiom]

As Bob says names are unsuitable as keys. It is not only bad since you may get two employees with the same name but it lowers performance.

When a text field is searched or joined Access must compare character by character with both upper and lower case. Keys should be long integers which Access handles at very high speed.

Also as Bob says, one does not store derived values such as full name. I am surprised that he didn't mention the word "Normalization". Search the forum for that term because it is a fundamental concept of database design and you are breaching its tenets.

 

[rmulder]

nevermind, i was able to do it. made a backup b4 of course. My forms and such will take some rework now but I think I'll be better off in the long run.

Another questions though... I still have not implemented a validation to make sure when a "new employee record" is being created, that somebody else has not already beaten them and added the new employee to the system. As my databases are used by multiple employees at the same time, this stuff happens.

Any ideas for a easy way to do this. I'm thinking the Beforeupdate event, I'm just not sure how to accomplish it. I would think we'd do this by checking to see if the persons full name is in the database??? but not sure

 

[boblarson]

It would be worth the work to transition over. You would start by adding the autonumber field to the employees table (I'll call it EmployeeID). Then add the EmployeeID (as Long Integer) field to each of your other tables where the employee name is currently the Foreign Key.

Then you can run an update query on each table for the foreign key. You would link the employees table to the other table with the name as it currently is and set the foreign key field to use the EmployeeID from the Employees table like this (in the UPDATE area in the QBE grid):

    Field: EmployeeID
    Table: SomeotherTable
Update To: [Employees].[EmployeeID]
 Criteria:

 

[boblarson]

Yes, you can check using the Form's Before Update event. Something like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If DCount("*", "tblEmployees", "[FirstName] = " & Chr(34) & Me.FirstName & Chr(34) & " And [LastName]=" & Chr(34) & Me.LastName & Chr(34)) > 0 Then
      If Msgbox("Name already exists in the database.  Continue adding?", vbQuestion + vbYesNo) = vbNo Then
          Cancel = True
          Me.Undo
      End If
  End If
End Sub

So something like that would give the option to continue adding them even if it is the same name in case there are two of them. But you might want to have one more piece of info (Birthdate would be a good one) which can then help distinguish between the two if there are really two different people with the same name. Then you can check the name and the birthdate and make sure that it isn't the same one.

 

[rmulder]

I really appreciate your help bob! I am looking at the dcount function and it makes sense to me except for the first parameter "expr". http://office.microsoft.com/en-us/access-help/dcount-function-HA001228817.aspx explains it as "An expression that identifies the field for which you want to count records." You use "*" and I'm just wondering if you can give me a simpler explanation on that parameter.

I think I'll find this function useful when it comes to later in my project so I want to understand it

 

[rmulder]

ahhh frustrated trying to get the birthday in there. I know it has to do with my formatting and data types. not sure how i have to "delimit" it or whatever. been trying the # instead of quotes but no luck.

If DCount("*", "Employees", "[First_Name] = " & Chr(34) & Me.First_Name & Chr(34) & " And [Last_Name]=" & Chr(34) & Me.Last_Name & Chr(34) & " And " & [Birthday] = Me.Birthday) > 0 Then

Can you see what's wrong?

 

[Galaxiom]

It is often far simple to fully reference the control than concatenate its value. The Access deals with the tricky stuff before submitting the query to JET/ACE.

If DCount("*", "Employees", "[First_Name]=Forms!formname.First_Name And [Last_Name]=Forms!formname.Last_Name And [Birthday]=Forms!formname.Birthday") > 0 Then

Otherwise you need to format the date

.... [Birthday]=" & Format(Me.Birthday, "\#mm\/dd\/yyyy\#")

The advantage of the concatenation is that the code still works no matter what the context of the form's instance.

 

[boblarson]

You use "*" and I'm just wondering if you can give me a simpler explanation on that parameter.

We use the "*" in the DCount because we don't need to worry about a field. We are only interested in how many total RECORDS there are which match our criteria. That syntax, while not technically necessary, seems to work when designating a particular field does not.

If DCount("*", "Employees", "[First_Name] = " & Chr(34) & Me.First_Name & Chr(34) & " And [Last_Name]=" & Chr(34) & Me.Last_Name & Chr(34) & " And " & [Birthday] = Me.Birthday) > 0 Then

Can you see what's wrong?

Yep, you are missing some date delimiters, your quotes are out of place, and you are missing a couple of ampersands (&):

If DCount("*", "Employees", "[First_Name] = " & Chr(34) & Me.First_Name & Chr(34) & " And [Last_Name]=" & Chr(34) & Me.Last_Name & Chr(34) & " An[B][COLOR=red]d [[/COLOR][/B]Birthday] = [B][COLOR=red]#" &[/COLOR][/B] Me.Birthday [B][COLOR=red]& "#"[/COLOR][/B]) > 0 Then

 

[Galaxiom]

We use the "*" in the DCount because we don't need to worry about a field. We are only interested in how many total RECORDS there are which match our criteria. That syntax, while not technically necessary, seems to work when designating a particular field does not.

Using * is faster because it simply counts the records. If a field is designated, Access counts only the records that are not null in that field.

 An[B][COLOR=red]d [[/COLOR][/B]Birthday] = [B][COLOR=red]#" &[/COLOR][/B] Me.Birthday [B][COLOR=red]& "#"[/COLOR][/B]) > 0 Then

Ah. I forgot it is a DCount. My code was for SQL so it may well be wrong.

The DCount is an Access function and would use the regional date format so does not require the change to mm/dd/yyyy, just the hashes.

Should still work for the US but maybe not regions with other date formats.

 

[rmulder]

I got all this. Thanks guys! Another related question though. I realized I was breaking another rule of design. When I used the "Full_Name" as a key field, That's what I would tie to "Time_Off" records. But I just realized now that I have the "autonumber" as the "key" for the "employees". I should prob tie my other records to that number instead of a full name which is not always going to be 100% unique.

Now how would I accomplish this. Because right now I have a combo box that looks up its values from the "full_name" field in the "employees_full_name" query i made with bobs help and stores that to the "time_off" record. Should I instead store the ID?

 

[boblarson]

You would just

1. Create a field (Long Integer datatype) in each table that needs to change.

2. Create an Update query (do each table one at a time - you don't need to save the query since it is only a one-time thing for each table that needs it. You would have the table with the Autonumber in the query and the other table as well. Link the NAME fields and then set the table's update area to be like this (this is what the QBE - query by example - grid would look like if your employees table is named Employees and the autonumber field is named EmployeeID:

Field:        [EmployeeID]
Table:        YourTableNameHere
Update To:    [Employees].[EmployeeID]
Criteria:
or:

 

[Galaxiom]

You will also need to change your combo design. It might seem easier to recreate it with the Wizard but I would suggest you do try to impliment the changes manually.

The RowSource query will need both the EmployeeID and (derived) FullName.
Then adjust the ColumnCount, BoundColumn and ColumWidths properties so the value of the combo (the BoundColumn) is the EmployeeID and the FullName is displayed.

The associated code will also have to be changed because you will not only have new fieldnames but the datatype will change from String to Long. Unfortunately changes rattle though the system and can take some time to find and fix them all. But it is better you do now than let the task increase as you add functionality.

A really useful free tool for locating reference to changed terms is V-Tools Total Deep Search.

 

[rmulder]

Ok, so if I understand right, then I am binding only the Employee ID to the "time_off" record. The "Full_Name" will not be stored to the "time_off" record anymore so I can delete this field from my "time_off" table?

 

[boblarson]

The "Full_Name" will not be stored to the "time_off" record anymore so I can delete this field from my "time_off" table?

That is correct.