How do I parse this?

[BennyWong]

Hello All,
I am currently using Microsoft Access 2000. I have a table named tblclient. In the table I have fields named:

FirstName
MiddleInitial
LastName
ETC....

I have a number of data sources that I would like to import into tblclient. The data sources are a combination of Excel file and Access database format. The problem that I have is the data sources has the FirstName, MiddleInital, and LastName in only one field as Name. How do I parse this into the respective fields as FirstName, MiddleInitial, and LastName? There is also another twist in this some do not have MiddleInitial. What is the procedure do I need to accomplish this task...query, vba, sql, macro?
Thanks for you assistance in advance.

 

[MichaelRed]

A quagmaire of exception processing awaits you. the subject has been often overdiscussed in these fors (Ms. A. and VB sections). I'll suggest that you use the (advanced) search with key words "LIKE" [FName | LName | FirstName First Name | LastName | Last Name | MI | Middle Inital | ... ]. Be sure to use various comninations of the above and select "Any Date" for the time frame. You will get way many more hits than the site search will actually display (I think it limits to 100), however even these will, with some review, will provide additional key words to refine your search.

If / when you have reviewed the existing body of literarure re the topic and developed at least a basic strategy, please return to these fora with seperate specific questions as necessary.

Finally, I can say that I have yet to see any complete programmong soloution to the overall / generic situation. At best, from my own experience and perusal of other 'soloutions' offered herein, you will get need a careful review of the results and a considerable manual correction process after the programmatic porcess.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[KornGeek]

My first step in solving a problem like this is to ask in plain English (no programming code), can you describe for me the rules for how to parse the data? Imagine trying to give instructions over the phone (or internet) to somebody doing it manually.

For instance, is the last name always first? Is a comma always used to show the end of the last name? How could somebody know whether something is a middle initial or part of the first name? Do any of the names have prefixes (such as Mr., Mrs., Ms., Dr., etc.) or suffixes (such as Jr., III, M.D., etc.)?

If you can explain the rules and have them apply 100% of the time, then it can probably be coded. If there are no rules, then there is no way for a computer to figure it out either.

Or, as I've told customers, "If you can't explain to me how to do this, how can I tell a computer how to do it?"