Re-Organizing Data

[Rozza]

Hi,

I have been sent some customer information which I want to incorporate into a larger database. The problem that I have is that the customer name (in its entirety) is stored in a single field and I want to be able to break it down into Title, Forename, Surname etc. Also some of the names are company names instead of person names.
Examples of some of the names are shown below:

T E JEFFREY FARMING
M/S J&R HUTCHINSON & SONS M
MR B W ROBSON
MRS P E HEATHCOTE
MESSRS A N & M A WILSON M
MESSRS D & R WOOLHOUSE
MR R W SHANN JNR

Does anyone have any idea on how to achieve this?

Cheers

Paul

 

[allanon]

What I think is that you have a BIG problem. The first thing you might try to do would be to identify all of the personal names. If they all prefix with 'MR' or 'MRS' etc then that may not be too big a deal. Just use InStr. There are other ways as well. Then at least you will have personal names and company names separated.

Next you would have to determine how you want to break up the names. Company names you would leave alone. Personal names you would have to find the space and then parse out the names. Not too had. You might need to come up with a couple of rules for certain things like middle initials, etc.

 

[JeremyNYC]

Yeah...hire a first-year college kid for peanuts.

Seriously, though, those data look pretty scattered. There are lots of code tidbits floating around here and tons in comp.databases.ms-access that will get you close. Or at least advance the cause incrementally. But with that many possible formats (and, of course, many more), no one routine is going to take care of all of it.

I would suggest combing through the archive here and at cdma for code people have already written and looking at what will help you the most. And thinking of ways that you can do this incrementally, such as making each routine operate only on records that will be helped by that particular routine, and using one routine and then the other.

And then get the college kid to do the clean-up work, as there will still be a lot of it.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.