Concatenate two fields into one in same table

[Guest]

Hi,

I have a table made of the following fields: First_name, Last_Name, User_ID.

I would like to concatenate the First_Name and Last_Name fields into a new
field 'Name' in the same table. So basically creating a new column called
Name that has the values of First_Name and _Last_Name concatenated.

Is this possible?

Many Thanks

 

[Guest]

Why?

Why not just do it in a query:

Name: [First_Name] & " " & [Last_Name]

I think the easiest way to do it would be to create an update query.

 

[Guest]

Hi Callie,

Is this possible?

Yes.
Is this advisable? No, for a couple of reasons. First, the concatenated
result is calculated. In general, you do not want to store the results of a
calculation in a table. The reason is that if one of the independent values
is later changed (for example, a person gets married or divorced and changes
their last name), the value stored in the calculated field will not be
automatically updated. Here is a quote that I like to share from database
design expert Michael Hernandez, author of Database Design for Mere Mortals:

http://www.seattleaccess.org/
(See the last download titled "Understanding Normalization" in the
Meeting Downloads page)

<Begin Quote (from page 23 of document)>
"The most important point for you to remember is that you will always
re-introduce data integrity problems when you de-Normalize your structures!
This means that it becomes incumbent upon you or the user to deal with this
issue. Either way, it imposes an unnecessary burden upon the both of you.
De-Normalization is one issue that you'll have to weigh and decide for
yourself whether the perceived benefits are worth the extra effort it will
take to maintain the database properly."
<End Quote>

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."

The second reason that it is not advisable is related to the field name that
you indicated: Name. This is a reserved word in Access. You should avoid
naming anything in Access with reserved words.

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Also, see this KB article:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

You can always concatenate the first and last names on-the-fy. Just create
an expression in a query, something like this:

CustName: [FirstName] & (" " + [LastName])
or
CustName: ([FirstName] + " ") & [LastName]

depending on the desired result.

For the first expression, if the [LastName] field is null, then (" " +
[LastName]) will still be null, since a null plus anything is null, so you
end up with just FirstName. However, if the FirstName is null and LastName is
not null, then you end up with a leading space in front of the LastName.

For the second expression, the situation is reversed. A FirstName without a
LastName would appear to resolve to the FirstName only, but in fact it would
be the FirstName plus a space.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Hi Tom,

Thank you for your reply.

Yes, you are right, it would de-normalize the table.

I wil take on your suggestions and let you know.

In the meantime I did manage to populate the field with the concatenated
fields with an UPDATE query:
UPDATE tbl_Accounts SET tbl_Accounts.Name = First_Name+" "+Last_name;

Hi Callie,

Is this possible?

Yes.
Is this advisable? No, for a couple of reasons. First, the concatenated
result is calculated. In general, you do not want to store the results of a
calculation in a table. The reason is that if one of the independent values
is later changed (for example, a person gets married or divorced and changes
their last name), the value stored in the calculated field will not be
automatically updated. Here is a quote that I like to share from database
design expert Michael Hernandez, author of Database Design for Mere Mortals:

http://www.seattleaccess.org/
(See the last download titled "Understanding Normalization" in the
Meeting Downloads page)

<Begin Quote (from page 23 of document)>
"The most important point for you to remember is that you will always
re-introduce data integrity problems when you de-Normalize your structures!
This means that it becomes incumbent upon you or the user to deal with this
issue. Either way, it imposes an unnecessary burden upon the both of you.
De-Normalization is one issue that you'll have to weigh and decide for
yourself whether the perceived benefits are worth the extra effort it will
take to maintain the database properly."
<End Quote>

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."

The second reason that it is not advisable is related to the field name that
you indicated: Name. This is a reserved word in Access. You should avoid
naming anything in Access with reserved words.

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Also, see this KB article:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

You can always concatenate the first and last names on-the-fy. Just create
an expression in a query, something like this:

CustName: [FirstName] & (" " + [LastName])
or
CustName: ([FirstName] + " ") & [LastName]

depending on the desired result.

For the first expression, if the [LastName] field is null, then (" " +
[LastName]) will still be null, since a null plus anything is null, so you
end up with just FirstName. However, if the FirstName is null and LastName is
not null, then you end up with a leading space in front of the LastName.

For the second expression, the situation is reversed. A FirstName without a
LastName would appear to resolve to the FirstName only, but in fact it would
be the FirstName plus a space.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hi,

I have a table made of the following fields: First_name, Last_Name, User_ID.

I would like to concatenate the First_Name and Last_Name fields into a new
field 'Name' in the same table. So basically creating a new column called
Name that has the values of First_Name and _Last_Name concatenated.

Is this possible?

Many Thanks