Concatenate two fields into one in same table

  • Follow


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
0
Reply Utf 6/28/2007 11:42:03 AM

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
__________________________________________

"callie_sunrise" wrote:

> 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
0
Reply Utf 6/28/2007 12:04:02 PM

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;


"Tom Wickerath" wrote:

> 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
> __________________________________________
> 
> "callie_sunrise" wrote:
> 
> > 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
0
Reply Utf 6/28/2007 1:12:03 PM

2 Replies
549 Views

(page loaded in 2.42 seconds)


Reply: