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
0
Utf
6/3/2010 11:35:15 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
4050 Views

Similar Articles

[PageSpeed] 21

On Thu, 3 Jun 2010 16:35:15 -0700, Iram <Iram@discussions.microsoft.com>
wrote:

>Hello,
>
>Access 2003. I have a single field called "Name" in a table called "Members".

Ouch. Name is a reserved word - a table has a Name property, a field has a
Name property, a form control has a Name property... Access can and will get
confused! I would very strongly recommend revising your table structure to
have separate fields for the components of the name: FirstName, MiddleName,
LastName, Suffix (e.g. Jr., III).
>
>There is about 300 records in a Last Name First Name format, example

Good that there are not too many, this can be a fair bit of work to handle the
exceptions.

>Doe John
>Moore Dave
>Johnson Jacob Mario

How about Mary Jo Johnson (first name Mary Jo, just ask her); or Hans ten
Broek (first name Hans, last name ten Broek); or Wing Men Li (you'll have to
find out whether he's using the Chinese tradition of family name first or has
turned it around and is actually a member of the Li family).

>(Some names have Middle Names or Sir's)

Sir Richard Featherstonehaugh Wembley-Fawkes III.... <shudder>

>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

I'd do this in a series of passes. Add the additional fields suggested, or a
reasonable variant thereof. First run an Update query:

UPDATE table
SET FirstName = Left([Name], InStr([Name], " ") -1), LastName = Mid([Name],
InStr([Name], " ") + 1)
WHERE [Name] LIKE "* *";

THis will parse out all the simple two-word names. With only 300 I'd then just
run a query with a criterion

LIKE "* *"

on LastName to select three- or more-word names; you can manually edit them.
Or you can run an analog of the query above to populate middle and last name,
and then carefully edit the records.
-- 

             John W. Vinson [MVP]
0
John
6/4/2010 12:38:57 AM
Thank you John W. Vinson!



"John W. Vinson" wrote:

> On Thu, 3 Jun 2010 16:35:15 -0700, Iram <Iram@discussions.microsoft.com>
> wrote:
> 
> >Hello,
> >
> >Access 2003. I have a single field called "Name" in a table called "Members".
> 
> Ouch. Name is a reserved word - a table has a Name property, a field has a
> Name property, a form control has a Name property... Access can and will get
> confused! I would very strongly recommend revising your table structure to
> have separate fields for the components of the name: FirstName, MiddleName,
> LastName, Suffix (e.g. Jr., III).
> >
> >There is about 300 records in a Last Name First Name format, example
> 
> Good that there are not too many, this can be a fair bit of work to handle the
> exceptions.
> 
> >Doe John
> >Moore Dave
> >Johnson Jacob Mario
> 
> How about Mary Jo Johnson (first name Mary Jo, just ask her); or Hans ten
> Broek (first name Hans, last name ten Broek); or Wing Men Li (you'll have to
> find out whether he's using the Chinese tradition of family name first or has
> turned it around and is actually a member of the Li family).
> 
> >(Some names have Middle Names or Sir's)
> 
> Sir Richard Featherstonehaugh Wembley-Fawkes III.... <shudder>
> 
> >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
> 
> I'd do this in a series of passes. Add the additional fields suggested, or a
> reasonable variant thereof. First run an Update query:
> 
> UPDATE table
> SET FirstName = Left([Name], InStr([Name], " ") -1), LastName = Mid([Name],
> InStr([Name], " ") + 1)
> WHERE [Name] LIKE "* *";
> 
> THis will parse out all the simple two-word names. With only 300 I'd then just
> run a query with a criterion
> 
> LIKE "* *"
> 
> on LastName to select three- or more-word names; you can manually edit them.
> Or you can run an analog of the query above to populate middle and last name,
> and then carefully edit the records.
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
6/4/2010 3:22:24 PM
Reply:

Similar Artilces:

Display last week of portfolio value on homepage?
Hi- Is there anyway I can set up a way to see the last week or so of my performance for my portfolio? Either a graph of the market value of the last 5 or 10 days or perhaps a graph of the % change. It would be helpful just to get a general sense of where things have been recently. -Dan You probably can't do this on the homepage (not in 2005), but the next best way would be to create a custom report to show you want you want to see. You might then be able to link to it directly from the customizable toolbar. -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check ht...

Jump to data in a list as first letter is pressed
I have couple of large lists with alphabetised data. Is it possible to have intellisense, like if P is pressed on the keyboard, it should jump to P, if H then jump to first name with H as the first letter, like that. It does not happen with the lists I have(by default)?Is this possible? Thnx Did anyone answer you on this? I am having the same problem... -- Siobhank ------------------------------------------------------------------------ Siobhank's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26133 View this thread: http://www.excelforum.com/showthread....

Detecting first row in ListView
Is there a method of detecting which row number is the first row displayed in a ListView when the view has been scrolled down? There doesn't seem to be an event that is fired when the user scrolls the list up or down. Is there another method of detecting the top row number in the view? Thanks, Charlie =?Utf-8?B?Q2hhcmxpZQ==?= <Charlie@discussions.microsoft.com> wrote in news:843217DD-4384-4250-853C-4A96C5D004D7@microsoft.com: > Is there a method of detecting which row number is the > first row displayed in a ListView when the view has been > scrolled do...

last files opened list
At one time I did a registry hack that disabled the recent/last open files list in the task pane. When I open excel the task pane is always empty under open. Does anyone know how to undo this tweak so that my last opened files show in the task pane? Using excel 2003. Thanks. Hi try to re-register Excel 1. Close Excel first and 2. On the Windows Taskbar 2.1 Start>Run "excel.exe /unregserver"(no quotes)>OK. 2.2 Start>Run "excel.exe /regserver"(no quotes)>OK. -- Regards Frank Kabel Frankfurt, Germany Phil (purplehaz) wrote: > At one time I did a registry...

Worksheet Specific Range Name
How do I define a range name on a worksheet that is specific to that worksheet? i.e. I want to use the same range name on several worksheets. Regards, On Oct 19, 10:53 am, Alan1 <alan.do...@virgin.net> wrote: > How do I define a range name on a worksheet that is specific to that > worksheet? i.e. I want to use the same range name on several > worksheets. > > Regards, 1 Select the range of cells that you want to name. 2 Choose Name from the Insert menu, then choose Define. Excel displays the Define Name dialog box. 3 In the Names in Workbook box, enter the name of the c...

Can you switch a documnet from a poster to a letter?
Once you created a poster can you resize it to a letter? I know that you can resize the paper, but the test remains poster size. I would create a .pdf file with that poster and then using Reader, print and scale it down to letter size. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "o2binny" <o2binny@discussions.microsoft.com> wrote in message news:B7EC493A-5E9E-4485-A710-9C5748010316@microsoft.com... > Once you created a poster can you resize it to a letter? I know that you > can > resize th...

Last number in a column shows in the total/summay line cell
I am looking for a formula that would have the last number listed in a column listed in the total or summary row of the spreadsheet. Column A lists the month of the year Column B lists # of files pending Jan 10 Feb 12 Mar 7 Summary -- I want this to show the last number in Column B -- in this example 7. Thank you. hi in a cell of your choosing, enter.. =OFFS...

how to print defined cell names with comments, not numbers?
My overall task is to print comments at the end of the sheet that display the defined cell name instead of the cell number. Problems: How do I define all of the intermediary cells based on the row and column headings, and then how do I print the comments to display the defined name, instead of the number. For example, I would like my print out to display: Cell: "Defined Name" Comment: Instead of: Cell: B2 Comment: ...

So why are YOU switching or not switching to Quicken?
I have seen many posts from users who have switched to Quicken and others who have no plans at all to switch to Quicken. I have used MS Money for 10 years and didn't activate Money Plus until this year so I will get updates until the bitter end in Jan 2011. I'm hoping this thread will be useful to discuss reason(s) to switch or not switch that people might not have thought of by themselves. So here the reasons I have come up with for why someone would or would not switch to Quicken. What's yours? Reasons for switching - Upset Microsoft discontinued Money - Your onlin...

Have switched to Apple Mail!
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange Too many problems with syncing, missing email, etc. etc. <br><br>I've now switched to Apple Mail and it works WAY better! How easy was it to transfer all your old email and folders? 2008 was a big disappointment for me because I only got 1 minor feature I was waiting for (thesaurus access) and the HTML editing that nearly everyone asked for since 2004 came out was still missing. In fact, like Office 2007 on the PC, the user interface (in the other office applicat...

Named Range across several worksheets
I need a named range for a cell on each of 12 (months) worksheets that applies to the selected sheet, for example Cash on the selected sheet. If I select all the sheets (group) and enter the range name, it is specific for the sheet entered, such as Jan.Cash. Is there away around entering named ranges that refer to each sheet without having to enter into each sheet individually? I did find that if I copy Jan and then rename the sheet, the range assumes the sheet name. This is fine for initial creation but not latter updating. Ed Ron, Thanks so much - works as advertised. I was in the p...

find last row with data
I have an excel file that is looking at another file to get an array o info.Currently it is going to the first blank row then looking up on row and retrieving that info.I have pasted below the function as it i currently written .Can someone show me an editing of this that wil tell it to simply read the bottom row with data. Thankss-Greg Function FindBlankRow() x = 2 Do While ActiveSheet.Cells(x, 1) <> "" x = x + 1 Loop FindBlankRow = x - -- singlgl ----------------------------------------------------------------------- singlgl1's Profile: http://www.excelforum.com/me...

Edit named range
In Excel 2002, how do you edit the cell locations of a named range? Fitz Insert>Name...>Define Select the name, amend it and press add again -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS web: www.nickhodge.co.uk web: www.excelusergroup.org "fitz" <fitzthecat@gmail.com> wrote in message news:14f17e44-a3cb-4828-8027-ef994ca7a9eb@s19g2000prg.googlegroups.com... > In Excel 2002, how do you edit the cell locations of a named range? ...

delete multiple range names
I have a workbook with multiple sheets. The workbook was built by someone else and has multiple range names that are no longer needed. How can I delete all range names to start fresh? Hi, In excel 2007 , go to Formulas, Name manager, choose the name to delet and click delete "mnsue" wrote: > I have a workbook with multiple sheets. The workbook was built by someone > else and has multiple range names that are no longer needed. How can I > delete all range names to start fresh? The following macro will delete all range names in the active workbook S...

/3gb switch
On 2 brand new Exchange 2003/Windows 2003 installs, I have modified the boot.ini file to include the /3GB /USERVA=3030 Exactly as instructed on the knowledgebase. However, I still recieve the memory settings warning whenever the servers boot up. Any ideas? The following KB article should guide you through some other settings that can be modified to optimize memory usage. http://support.microsoft.com/default.aspx?kbid=815372&product=exch2003 -- Ben Winzenz Exchange MVP "Jorge Azcuy" <Jorge Azcuy@discussions.microsoft.com> wrote in message news:25EA3E6F-C66F-478A-...

Last day of the Month???
I am creating a timesheet and i have inserted the first day of the month with a combo box to a linked cell A7 then in the rest of the column i add 1 to A7 then 2 and so on for the whole month What i need is to do a total on the last day of the week for hours worked, but i have a problem with the first week and last of the month as they are not complete weeks as the month could start on a tue or a wed etc and finish on a thur so how do i get excel to make a total per week????? Also the position of the days change as the days of the month are automatically filled in from the first day ot the m...

switching to windows 7
switching to windows 7, still need to have access to old e-mails. how can i access e-mails created in outlook express? Copy the ***ENTIRE*** OE message store to flash or CD. Do not copy individual dbx files. Put the copy of the message store on the Win7 Desktop. Open WLMail and File | Import | Messages | Outlook Express and point to where you saved them. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Jessica" <jstech@optonline.net> wrote in message news:uOEamPygKHA.1112@TK2MSFTNGP04.phx.gbl... > switching to windows 7, ...

download matching names in Money 2004
How does the MS Money download match items to the investments in one's account? I have two accounts at Fidelity, one a brokerage account, and the other a 401k retirement (Netbenefits). Both happen to hold some of the same mutual fund. One refers to it as "SPARTAN EXTENDED MARKET INDEX". I was able to match this to the brokerage account. But the second account refers to it as "SPTN EXTND MKT INDEX". MS Money 2004 wants to refer to this as a different security and won't let me give it the same symbol. But of course it is the same symbol with the same price his...

cell address rather than range name
In formulas, I want to use the cell address for a named range rather than the range name. Ex: If cell A1 is named "price", I want the formula referencing it to use the cell address (A1), not the range name (price). That is, =A1 rather than =price. Excel defaults the range name, and I haven't found a way to replace it with the cell address (other than typing it in). Thanks. range("price").address "Angi Bemiss" wrote: > In formulas, I want to use the cell address for a named range rather than the > range name. Ex: If cell A1 is named "p...

switching
I am considering switching to MAC I recently bought Office for my PC. Do i have to re-purchase for MAC or are there any program from Microsoft to assist so i do not incur the double cost? thanks David In article <221401c49ca8$945b59b0$a401280a@phx.gbl>, "David Knopf" <knopfd@woh.rr.com> wrote: > I am considering switching to MAC > > I recently bought Office for my PC. > > Do i have to re-purchase for MAC or are there any program > from Microsoft to assist so i do not incur the double > cost? Unfortunately, there's no discount/switch ...

copying names of folders
hi, i wold like to copy all the folders i one personal folders to another but only the names of the folders and not what's in the folders. is it possible? thanks michal ...

switching from outlook express to outlook
I keep getting a mail delivery error, either saying "password required" or "the operation was cancelled". I've tried all combinations in the properties section of the account, to no avail. Of course, Express asks me for a password before kicking in. Any suggestions? "Barrie" <bhharry@telus.net> wrote in message news:05eb01c39539$a1626300$a401280a@phx.gbl... > I keep getting a mail delivery error, either > saying "password required" or "the operation was > cancelled". I've tried all combinations in the properties >...

IE8 says 'last session closed unexpectedly' and offers to restore
I never close down with IE open, so why does it keep asking to restore my last session? I have Windows 7, recently purchased, so no control over IE8. If I delete browsing history, I can use it, and open it over and over, but when I boot the machine, it won't allow me to open IE8 to my home page. Closing it with the task bar doesn't help - it just goes into a wait state the next time I try to open it. (p.s. signing up just to ask this question was a nightmare of unclear directions) "measurement" <measurement@discussions.microsoft.com> wrote in message n...

error after runing MS update last weel 10/13/2005
After getting update from microsoft I got error when trying sending email from MS excel program as attachment, with this error: general mail failure. quit microsoft exel, restart the mail system, and try again. ...

HELP. 2 Emails in 03' and need to switch them.... want new email M
Help! I have just added second email acct to 2003 + want new acct to appear so its connected to contacts,calendar,outbox... I'm unsure of how to explain it.... but I'll try. Note: I have already made this email account my default account. Currently, my view looks like this in "all mail folders" view under Mail in navigation pane: +Personal Folders Deleted items Drafts +Inbox ****(This is the inital email, and under OUTLOOK TODAY, THIS email account comes under inbox (seen under "messages" in Outlook Today.)**** Junk Email ...