How do I combine "First Name", "Last Name" Fields into a "Name" Fi

I have two tables one has First Name and Last Name fields. The other has 
first and last names combined in a "Name" Field. I would like to combine 
tables via a query for sorting and mailing purposes. This problem sounds so 
simple, but it is beyond me!!!!!

Please help!
0
Utf
10/15/2007 6:42:05 PM
access 16762 articles. 2 followers. Follow

3 Replies
3196 Views

Similar Articles

[PageSpeed] 42

Create a new query.  In design view, add the table that has both, then in a 
"field" on the grid, add something like:

    FullName: [FirstName] & " " [LastName]

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Climberjon" <Climberjon@discussions.microsoft.com> wrote in message 
news:176C54C9-A07B-4B58-9417-C272B5A6BBF6@microsoft.com...
>I have two tables one has First Name and Last Name fields. The other has
> first and last names combined in a "Name" Field. I would like to combine
> tables via a query for sorting and mailing purposes. This problem sounds 
> so
> simple, but it is beyond me!!!!!
>
> Please help! 


0
Jeff
10/15/2007 6:58:49 PM
On Mon, 15 Oct 2007 13:25:01 -0700, Climberjon wrote:

> Jeff You have me on the right track. AHow do I get a space between the 
> FirstName and the LastName. I see the " " you have added, but Access tells me 
> that it is a syntax error. Is there a value I should put in the quotes? 
> 
> Thank You for your reply.
> 
> "Jeff Boyce" wrote:
> 
>> Create a new query.  In design view, add the table that has both, then in a 
>> "field" on the grid, add something like:
>> 
>>     FullName: [FirstName] & " " [LastName]
>> 
>> Regards
>> 
>> Jeff Boyce
>> Microsoft Office/Access MVP
>> 
>> "Climberjon" <Climberjon@discussions.microsoft.com> wrote in message 
>> news:176C54C9-A07B-4B58-9417-C272B5A6BBF6@microsoft.com...
>>>I have two tables one has First Name and Last Name fields. The other has
>>> first and last names combined in a "Name" Field. I would like to combine
>>> tables via a query for sorting and mailing purposes. This problem sounds 
>>> so
>>> simple, but it is beyond me!!!!!
>>>
>>> Please help! 
>> 
>>

Jeff inadvertently left off an Ampersand.

FullName: [FirstName] & " " & [LastName]
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
10/15/2007 8:40:14 PM
Thnaks, Fred.

As you mgiht notiec, my fat fngiers don't alwyas wrk <g>

Jeff

"fredg" <fgutkind@example.invalid> wrote in message 
news:qc3dphzd15jp$.wnwfz36qch9v.dlg@40tude.net...
> On Mon, 15 Oct 2007 13:25:01 -0700, Climberjon wrote:
>
>> Jeff You have me on the right track. AHow do I get a space between the
>> FirstName and the LastName. I see the " " you have added, but Access 
>> tells me
>> that it is a syntax error. Is there a value I should put in the quotes?
>>
>> Thank You for your reply.
>>
>> "Jeff Boyce" wrote:
>>
>>> Create a new query.  In design view, add the table that has both, then 
>>> in a
>>> "field" on the grid, add something like:
>>>
>>>     FullName: [FirstName] & " " [LastName]
>>>
>>> Regards
>>>
>>> Jeff Boyce
>>> Microsoft Office/Access MVP
>>>
>>> "Climberjon" <Climberjon@discussions.microsoft.com> wrote in message
>>> news:176C54C9-A07B-4B58-9417-C272B5A6BBF6@microsoft.com...
>>>>I have two tables one has First Name and Last Name fields. The other has
>>>> first and last names combined in a "Name" Field. I would like to 
>>>> combine
>>>> tables via a query for sorting and mailing purposes. This problem 
>>>> sounds
>>>> so
>>>> simple, but it is beyond me!!!!!
>>>>
>>>> Please help!
>>>
>>>
>
> Jeff inadvertently left off an Ampersand.
>
> FullName: [FirstName] & " " & [LastName]
> -- 
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail 


0
Jeff
10/15/2007 10:36:20 PM
Reply:

Similar Artilces:

Field with both first name and last name
I have a table where the field PRODUCTION_DESIGNER is composed of a persons first and last name with a single space between them, for example "David Henderson" is in the contents. Select this field and truncate this to the initials of the first and last name and place into another field leaving the original field untouched. The new field would have "DH" as its contents. Thank you Use the expression Left(Production_Designer,1) & Mid(Production_Designer,Instr(Production_Designer," ")+1,1) If you want to do this in an update query UPDATE [Your Table ...

combining last name first name mid initial
I'm a teacher who is responsible for breaking down a file with 2000 students and 200 teachers in 5+ classes each into individual department files. There are three NAME cells - Last name, First name, Middle initial in each row. Row 1: Cell 1 - Jones; Cell 2 - Mary; Cell 3 - Z. I need to have those three cells for each student combined into one cell with a formula so that I can cut and paste a section of students into another excel file so that textbooks can be scanned. Row 1: Cell 1 - Jones, Mary Z We've done it in the past, but we've lost the formula. Thanks for saving my ...

if I know the last name can I get the first name from the full name?
I'm inporting data that has the person's full name in one field. i.e. "GLORIA DE LOS SANTOS". But they have given me a last name in a separate field, and it's listed as "LOS SANTOS". Since I know the last name, can I extract everything excluding the last name from the full name? I don't mind if they included the middle initial and that was on the end of the first name. I could live with that. Thanks Kelvin Well, one way to do it is just use the length function and pull from the full name field the field length of the full name field minus length ...

Change entries from last name, first name
I have to combine my list of almost 800 names listed as last name, first name, with another list which has them as first, then last. Can I somehow correct my entries to read as first then last? Thanks much, djlny djlny wrote: > I have to combine my list of almost 800 names listed as last name, first > name, with another list which has them as first, then last. > > Can I somehow correct my entries to read as first then last? > > Thanks much, > djlny If you have the last name, first name in one cell I would use data > text to columns to separate them into two ...

How do I convert last name, first name in cells to first name las.
Cells contain names in last name, first name order. I wish to convert to first name last name (no comma) without retyping. Thanks Assuming your data is in column A. Select the names in Column A. Make sure that Columns B and C are blank. In Excel 2003, click on data, text-to-columns and choose delimited and comma. The last name of the first entry will be in A2 and the first name will be in B2. In C2 type =B2&" "&A2 to concatenate the name with first name first and then copy down the formula in C2 as far as necessary. As a last step be sure to copy Column C, go ...

FULL NAME in contacts not populating FIRST NAME and LAST NAME corr
I have Office XP with Outlook 2002 SP3 installed and whenever I type a name in to the FULL NAME field on the contacts form, the form puts the first name in the LAST NAME field and the last name in the FIRST NAME field. For example entering "John Smith" in the FULL NAME field and pressing the FULL NAME button results in the CHECK NAME FORM displaying Smith as the FIRST NAME and John as the LAST NAME. This happens no matter what the settings are in the FILE AS box. It seems to me that you should be looking at the settings you specified for the Full Name field, not the File As...

Change Fileas name to first name last name
Hi Where can I change the settings so that the contacts filed as First Name last name instead of Last name,First name Thank Jason OL2002: How to Change the File As Field for All Contacts ID: 291144 -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply to this newsgroup. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm "Jason" <anonymous@discussions.microsoft.com> wrote in message news:7F277C...

convert from last name, first name TO first name, last name
How can I convert my contacts (as a group menaing all of them at once0 from the last name, first name to first name last name, without having to enter EACH cotnact and perform it almost 400 times? Also, in what format would you suggest trying to print my contacts showing fields name mobile, home, work phone and email address? I assume there is a way to make used defined fields? You'll need to create a post with enough information that others could understand it. How are we to know which field or view you are trying to "convert?" Be specific. I would suggest pri...

Fix the order of the last name and first name
How do I fix the order of the names, when the last name is first and the first name is second, separated by a comma. Hi! What order do you want them in? Biff >-----Original Message----- >How do I fix the order of the names, when the last name is first and the >first name is second, separated by a comma. >. > Hi try =TRIM(MID(A1,FIND(",",A1)+1,255)) & " " & TRIM(LEFT(A1,FIND(",",A1)-1)) -- Regards Frank Kabel Frankfurt, Germany Zami wrote: > How do I fix the order of the names, when the last name is first and > the first na...

Excel Newb: Splitting First name and Last name
I have a long list of names in column A that look like this: Ana Trujillo Antonio Moreno Thomas Hardy Christina Berglund How can I split out the names by first name and last name with first name going in B and Last name going in C? Thank you very much. You can use the Text To Columns tool on the Data menu, or you can use formulas. If the full name is in A1, you can get the first name with =LEFT(A1,FIND(" ",A1)) and the last name with =MID(A1,FIND(" ",A1)+1,99) However, you need to decide what to do with names with more than two components. E.g., "John David Smit...

last name, first name strings
:confused: Hi there, I am a newbie and am having some trouble with seperating data in Excel. Here is my issue: I have a table of employee names are in one column alone, but all in a different format: first name -space- last name last name, first name last name -space- first name What I am needing to do is get all the names to be in the same order and seperated into two columns as: Last Name column First Name column How do I take those combined name formats that are in one column and seperate them into seperate columns in the correct order? Thanks, Christina :confused: -- data_div...

What formula will take a name in one cell (last name,first name) .
I have a name in a cell, last name first then a comma then first name, and I want to seperate them into two cells. I know there is a formula to do that, but I don't know what it is, can anyone help me? Use the menu option Data:Test To Columns to parse the one field into two fields Good Luck Stewart "jobby" wrote: > I have a name in a cell, last name first then a comma then first name, and I > want to seperate them into two cells. I know there is a formula to do that, > but I don't know what it is, can anyone help me? You could use Data > Text to colum...

Sorting by Last Name and then First Name in the Same Field
I have "inherited" a database where the first name and last name are contained in the same field. 1. Is there any way to separate these two in items into two fields? 2. Is there any way to sort in the same field by last name and then first name? I can only get it to sort by the first name since the first name is first in the field. Any assistance would be much appreciated!! On Wed, 5 Dec 2007 20:16:00 -0800, RS wrote: > I have "inherited" a database where the first name and last name are > contained in the same field. > > 1. Is there any way to s...

OL2002
I have my Contacts in Outlook 2002 showing as Last name, First name. How do I change them all (about 300 contacts) in one easy go so they are in the order: First name Last Name I would hope I don;t have to manually do them all one by one!!! You might want to tell us what view you are referring to. Rather hard to guess. -- Russ Valentine [MVP-Outlook] "��" <news@kidsnews.au.org> wrote in message news:41994c52$0$87832$65c69314@mercury.nildram.net... >I have my Contacts in Outlook 2002 showing as Last name, First name. > How do I change them all (about 300 contacts) i...

How do I combine "First Name", "Last Name" Fields into a "Name" Fi
I have two tables one has First Name and Last Name fields. The other has first and last names combined in a "Name" Field. I would like to combine tables via a query for sorting and mailing purposes. This problem sounds so simple, but it is beyond me!!!!! Please help! Create a new query. In design view, add the table that has both, then in a "field" on the grid, add something like: FullName: [FirstName] & " " [LastName] Regards Jeff Boyce Microsoft Office/Access MVP "Climberjon" <Climberjon@discussions.microsoft.com> wrote in mes...

Extracting First Name from cell with first name and last name
I have searched several posts and am still stumped. I have a first name and last name in Cell B10 on Sheet1. I want to extract the first name with no spaces. It is part of a bigger code where I am opening outlook and making an email. The end result is to use the person's first name in the saluation. I have all the code to do everything except that I can't figure out how to grab the first name. Any help would be greatly appreciated. Thanks in advance. Matt You probably want this... =LEFT(A1,FIND(" ",A1&" ")-1) but be advised that it (and m...

Switch Last Name First Name to First Name Last Name
Hello, Access 2003. I have a single field called "Name" in a table called "Members". There is about 300 records in a Last Name First Name format, example Doe John Moore Dave Johnson Jacob Mario (Some names have Middle Names or Sir's) How could I run a query to change the names to First Name Last Name? Or if you recommend in Excel how would I do this? John Doe Dave Moore Jacob Mario Johnson Thanks. Iram On Thu, 3 Jun 2010 16:35:15 -0700, Iram <Iram@discussions.microsoft.com> wrote: >Hello, > >Access 2003. I have a si...

how do I reveerse name order last name, first name to first name, last name
I have a long database, 27k with names that I want to mail to. The list is last name, first name. I want to do a global change to first name, last name. I also want to add "Mr" to it. For example: the list now is Smith, John. I want to change it to Mr. John Smith for the entire list. Assuming there is only one comma in each: ="Mr. "&TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))&" "&LEFT(A1,FIND(",",A1)-1) Note that some of the women might be offended.... 27k names, all male? Scott Don Smith wrote: > I hav...

Contacts list
Is there a way in OL2000 to have the contacts display as last name first, then first name last as in the Global? I found this for http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B319142 for OL2002 so could I assume it can't be done in OL2000? We have Exchange 2000 with OL2000. TIA, Joe Go to the Mail icon in Control panel, select your contact list and select Properties. From there you can select first/last or last/first. It only seems to work from here and not from within Outlook. >-----Original Message----- >Is there a way in OL2000 to have the contacts displa...

Merge different first name, same last name, same address
I have 3291 names and addresses. In order to avoid creating duplicate labels, I want to merge rows that have the same last name and street address but different first names. Fr: Jack Smith 123 Elm Street Jill Smith 123 Elm Street To: Jack Jill Smith 123 Elm Street If the entire name and address is within one cell, you can use the Data/Text-to-Columns function, and then sort by the address cell. -- LACA ------------------------------------------------------------------------ LACA's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30381 View this thread: h...

Last Name & First Name
I have Excel 2003. Is the a way to combine two columns Last Name and First Name to make a Name field where the First Name and Last Name are just separated by a space for printing a list? Thanks for information. Susan Yes there is - I like this feature. Create a blank column to consolidate the two columns. Under Function, use CONCATENATE. In Text 1, put the cell number, in Text 2- space, in Text 3, the next cell number. Remember, you will need to have a field for the space or there won't be one. HTH >-----Original Message----- >I have Excel 2003. Is the a way to combin...

Last name, first name
I have a list with lastname, firstname in a cell. I would like to change it to firsname lastname. Any clues? officexp. Thank you. Mich - Insert a column after your name column, highlight the name column, go to Data, Text to Columns, Delimited, select comma, finish. This will give you two columns, lastname, firstname. Insert a column before your lastname column. Move the firstname column to the new column. Your third column should now be blank. Enter in the formula =CONCATENATE(,A1," ",B1)in the third column. This will put your firstname space lastname in the third ...

First Name Last Name
I have a form to enter a First Name Last Name from a keypad on screen. It works fine to input both First and last name into one field; however is there a way that when I save the record I can split the first name and the last name into seperate fields? I know I can enter them seperatly but I'm trying to do it in one shot. Is there a function that finds a space between two words, then perhaps another function to enter whats left of the space, then another to enter whats on the right? Any help is appreciated Thanks DS On Sat, 21 Jul 2007 16:29:52 -0500, DS <bootybox@optonline.net...

Last Name, First Name
In version 1.2 we incorreclty set up the database as Last Name, First Name. I see in Version 3 going forward we change this for new records. Is there a way I can go into the database and dorrect this to First Name LAst Name, and then change the format going forward. Thanks Kim In both versions, there is a setting under the system settings to control this for ongoing updates. Then, you can issue a direct SQL update on the underlying ContactBase & LeadBase tables to update the fullname field to be the correct value. Be carefule updating the tables though. This is not a supported ...

Separating First Name(s) and Last Name into Separate Columns
I have a spreadsheet with one column ("name") that contains names in the following formats occuring randomly down the list: Firstname Lastname Firstname & Firstname Lastname Firstname Lastname & Firstname Lastname I need to get them all into two new columns "firstname" and "lastname" and I'm having difficulties writing a formula to account for all 3 scenarios in one cell. (If I have to do it in multiple steps - so be it!) For the 1st example, the contents of the new "firstname" column should be Firstname, while the "lastname"...