Concatenate two fields into one in same table

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
Utf
6/28/2007 11:42:03 AM
access 16762 articles. 2 followers. Follow

2 Replies
916 Views

Similar Articles

[PageSpeed] 21

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
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
Utf
6/28/2007 1:12:03 PM
Reply:

Similar Artilces:

When concatenating concatenates don't concatenate...
Hi List, Can anyone help? When concatenating already-concatenated cells, th result displays perfectly well in the Excel spreadsheet, but truncate when the cell is pasted into a .txt file. It doesn't seem to be due t Data Validation limits (having said that, selecting the entir worksheet and doing Alt > Data > Validation > Validation criteria Allow = Any value" did seem to solve the problem once, but only to com back next time round). The truncation occurs sometimes after 8 or 1 chars, and sometimes after 20 or so, always at the same spot. If I cop the cell into a fresh Excel...

print report according to value field
I am trying to print a report depending on a the field "OrderQty". i did created the code with the print command and the criteria and it looks like this: DoCmd.OpenReport "rptProduction", acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip DoCmd.PrintOut , , , , [OrderQty] however, it give me an error saying, that "|" field is not found. i looked through the code and tested the code, by putting 1 or 2 in place of OrderQty and it prints fine, that means to me that it's not the code and i looked to through the Query that the source of that R...

how to compare two bitmaps and give a mark to describe how similar they are
hi, everyone how to compare two bitmaps and give a mark to describe how similar they are? The two bitmaps's size are the same, I can use CDC::GetPixel to get a mark, but this method is inefficient. Any body have any ideas? Thank you very much. take the pixel value of one minus the pixel value of the other and square this difference. So it goes something like difference = 0; for (x=0....) for (y=0....) difference += pow( bitmap1(x,y) - bitmap(x,y) ), 2) You could also use a cross correlation, but that has a built in bias such that a difference around white is more import...

Text Field Validation Rule for Date
Hi, I have a RESPONSE field on my table. This field is set up as a text field (I do not want to change it due to specific requirements)- however, I'd like to set up a validation rule to determine whether or not it is a valid date: mm/dd/yyyy. What should I input in the validation rule? Thanks I do not think it is possible at table level but in your data entry form us AfterUpdate event and IsDate function. -- Build a little, test a little. "shm135" wrote: > Hi, > > I have a RESPONSE field on my table. This field is set up as a text > fiel...

Compare Two Workbooks
I'm trying to write a piece of VBA that will check each cell in a workbook against each corresponding cell in another workbook, and highlight where there are differences. I'm really falling over at the first hurdle, as I cant get my head around how to reference the cells. I've got variables that tell me the workbook, the worksheet, the row and the column, but I don't appear able to so something as simple as check if wb1.ws1.cell1 = wb2.ws2.cell2. The code I have so far is below: Sub test() Dim wb1 As Workbook Dim wb1name As String Dim wb2 As Workboo...

VBS script, Sync two folders accross networks
Hi, I need a VBS script to sync to folder accross a network. So the source would be \\servername\sharename\syncfolder and the destination would be \\server2\share2\sync2. Can someone please help. Submitted via EggHeadCafe - Software Developer Portal of Choice A Good Solution for "Magic String" Data http://www.eggheadcafe.com/tutorials/aspnet/b916e3a9-d056-4669-8bf3-aa98ed6669c3/a-good-solution-for-magi.aspx "mattthew Pitera" schrieb im Newsbeitrag news:2009129181729matt.pitera@gmail.com... > Hi, > I need a VBS script to sync to folder accross a net...

How to create a combo box in a table
OK I will try and make this as detailed as I can I have a form in that form there is a sub form in a tab, that sub form would contain a data sheet that has a drop down Box , that drop down box selects a given product and up dates the price in one of the colums in the data sheet, by then entering your quantity it would then give you a total and that record apon hitting a command button would then be recorded. Here is my problem I am able to create a form based on a table, placing a combo box that will up date the text boxes with the selected info, but Im not able to get this proces int...

Nesting or joining two formulas???
I currently have two formulas that I'm needing to somehow join togethe but I have not been able to do this legally. {This formula gives a sum of any time over 8 hours and displays i using a decimal value.} My cells are formatted to 0### for cells C10 through F10 and cell H1 is converted to decimal using the formula below. [this formula located in H10] =IF((F10-E10)+(D10-C10)>800, (F10-E10)+(D10-C10)-800, 0)/100 {I still needed the above formula to display in quarter hou increments, ie: .15 = .25, .30 = .50 etc. This was submitted to me b JanetW and works great seperately when pla...

Two columns into one row
I have some files I need to format. I have a list of coordinates that are in two columns and I need to turn them into one row, keeping the order ie: 1 2 3 4 5 6 7 8 9 10 to 1 2 3 4 5 6 7 8 9 10 Is there anyway I can convert these two columns into rows without cutting and pasting the data individually? -- alih ------------------------------------------------------------------------ alih's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36023 View this thread: http://www.excelforum.com/showthread.php?threadid=558128 This will do what you want =A1&&qu...

Pivot table #16
Using 2003 Column A holds WBS codes. Column B holds WBS description. Column C holds manhours for the selected WBS. I want to run a pivot that shows a sum of the manhours for each WBS. When I run the pivot wizard, I get the "number" of WBS codes that hold manhours but not a sum of the manhours. How can I get a sum of manhours per WBS? Glen Hi Glen Double click on the Manhours filed, and select Sum instead of Count -- Regards Roger Govier <glen.e.mettler@lmco.com> wrote in message news:1168533927.695015.278050@o58g2000hsb.googlegroups.com... > Using 2003 > C...

Concatenate function
Hi, How can I concatenate these 2 cells: one is time and the other is text: 9:00 and AM and I want the result to be 9:00 AM =CONCATENATE(AD2, " ", AE2) I have tried different formating cells but it doesn't work, this is what I get: 0.375 AM Thanks for your help. NSNR - You must format the time (which is a number) to text: =TEXT(AD2,"H:MM") & " " & AE2 -- Daryl S "NSNR" wrote: > Hi, > How can I concatenate these 2 cells: one is time and the other is text: 9:00 > and AM and I want the result to be ...

Filltering data between two date ranges
Hi, wonder if anyone can help me - I have a list containing peoples birthdays. I wish to be able to enter a date range i.e. today and 2 weeks into the future. I then wish for the list to be filtered showing only those birthdays within the date range. The date range could span across 2 different months. I'm not particularly skilled in using features such as VBA etc, so if there is a simple solution it would be much appreciated. Many thanks for any help. Hi Dave! If you enter the birthdays as Month/Day this is somewhat easy. If you enter the birthdays as Month/Day/Year it is much m...

Concatenating Cells
I have spent hours this afternoon in Excel 2003 trying to concatenate two adjacent text columns into a third column defined as Text format. It doesn't work, the result cell just displays the formula you enter {e.g. =A1&B1 or =CONCATENATE(A1,B1)}. I discovered after a great deal of frustration that this will only work if the cell containing the formula is formatted as '*General'*. All the MS command help refers to the data being concatenated as 'text' data as does the command help that displays as you type. I found no help on this on the MS site and trawling the w...

Want to setup a Table of contents from text in a Table
1. When I select text in a cell, to format that text to a specific Style, then the whole row's formatting change to that specific Style. or 2. The program put some other text into the table of contents that I have not mark Hope someone will help me. Thanks Answered in the Tables group. -- Stefan Blom Microsoft Word MVP "Es" <Es@discussions.microsoft.com> wrote in message news:2D73B7F9-08BA-4876-A362-CA09056EC229@microsoft.com... > 1. When I select text in a cell, to format that text to a specific Style, > then the whole row's forma...

Lost Subject Field
I don't know how I did it but somehow I managed to delete the subject field in outlook so now when I go to create a new email to send to someone I only have the TO: line showing! I am unable to enter a subject! How do I get the subject back???? Go up to VIEW | MESSAGE HEADER. Kenny "Rockets" <wcrockets@hotmail.com> wrote in message news:00b601c3dc66$d8456fb0$a501280a@phx.gbl... > I don't know how I did it but somehow I managed to delete > the subject field in outlook so now when I go to create a > new email to send to someone I only have the TO: line &g...

Trouble auto populating field using DLookUp
I am trying to auto populate a field in a subform. I have 3 tables: schools TOWNS StudentsAttending I have a form with the source being the tbl schools and a subform with the source being tbl Students Attending. StudentsAttending is updated from the subform with the number of students from each town. I am trying to use the DLookup function with the AfterUpdate event to populate the towns geocode into a text box on the subform and then into the tbl StudentsAttending, but am having a great deal of difficulty. I am Very New to data base workings so I would appreciate any help ... Here is...

two more questions
Can I lock some columns on a sheet so they are not editable ? And I am new to excel and needed to calc several fields from different worksheets onto one "totals" worksheet. How do I do this? Thanks over and over again : ) Mary, by default all cells in excel are locked, if you only need a few locked I would select them all first, Ctrl A, then go to format, cells, protection and uncheck locked, then select the cells or columns you want to lock and go to format cells and check locked, the go to tools, protection, and protect sheet, enter a password if you want, now the cells that ar...

2 CNs for one project purchasing...
Dear all, can we generate 2 CNs for the same project purchase invoice in GP? Thanks for advice. Regards, kat ...

"Write Conflict" When Updating Table Behind Form?
Got a form with a subform based on a work table in C:\Temp. The subform shows a list of dates and a computed amount for each date. The amounts were computed into a field in the work table when the form was loaded. User changes a date. In txtDate_AfterUpdate, I then open up a recordset into that work table and re-compute the amount for each and every date. Problem is, that somewhere along the line my code is provoking: -------------------------------------------------------------------------------- Write Conflict: Record changed by another user since you started editing.... If you save, yo...

converting date and time fields
Hello, I have columns that contain date and time in the "14/07/2003 17:57:00" format. I need to convert them into date-only and time-only fields (not only visually). How do I get rid of the date/time part? Thanks thanks! "Laura Cook" <laura@top-brands.com> wrote in message news:eP$BBBzZDHA.2648@TK2MSFTNGP09.phx.gbl... > With date and time in column A, in B1 enter: > > =INT(A1) > > Format as a date. Now in C1 enter: > > =A1-B1 > > Format as a time. > > Copy the formulas down as far as needed. If you no longer need column A, > ...

Compare two sheets...
Any one know how can I compare two Excel Sheets? Thanks Compare them for what? If they are formatted the same and you are comparing numbers, copy one sheet, then select the numbers from the other sheet and go to this copy. Select the same area and do Edit=>PasteSpecial and select values and subtract. That will show you the numeric differences. -- Regards, Tom Ogilvy "Mike Hunt" <nojunk@please.com> wrote in message news:%23I0V0AAiDHA.2296@TK2MSFTNGP09.phx.gbl... > Any one know how can I compare two Excel Sheets? > > Thanks > > Go to this website an...

outlook 2003 blank To: field when replying to email
I am experiencing a problem in Outlook 2003 when I reply to a message the To: field is left off the email although the receipient does receive it. I've pasted an example below: ________________________________________ From: MattG Sent: Friday, February 13, 2004 7:50 AM Subject: voicemail Any help would be greatly appreciated, sorry if this topic has been discussed already. Thanks Again, Matt ...

Copy data from one sheet to another 02-25-10
I started out using the following code which I got from this site: Public Sub CopyDatatoDatabase() ' by Dave Peterson ' The code depends on the last used cell in column A for both ranges. Dim rngToCopy As Range Dim DestCell As Range Dim wbk As Workbook On Error Resume Next Set wbk = Workbooks("somefile.xls") On Error GoTo 0 If wbk Is Nothing Then MsgBox "Opening the book now" Set wbk = Workbooks.Open("somepath\somefile.xls") End If With ThisWorkbook.Worksheets("Data") Set rngToCopy = .Range("A2:I" &...

timestamp notetext fields
i need to add a timestamp to the bottom of the notetext field each time an activity window is open...lets say i open up an existing task i need to append timestamp to the bottom of the notefield. so it would look something like this: -------------------------------------------------------- subject: task1 29/09/2004 3:39pm some note text here -------------------------------------------------------- open up task1 for the 2nd time it should look something like this -------------------------------------------------------- subject: task1 29/09/2004 3:39pm some note text here 30/09/2004 11:5...

Unable to create tables or forms
Hi, I have a users using access 2003 and for some reason he's not able to create a table, form, etc. When he double clicks on it or right click on it to open it, nothings happens. I've tried "detect and repair" and uninstalling/reinstalling access to no avail. Any other suggestions? Thanks for your help in advance. CJ investigate the User Level Security settings -- NTC "CJ" wrote: > Hi, > I have a users using access 2003 and for some reason he's not able to create > a table, form, etc. When he double clicks on it or right click on it to o...