How to update a table with just SURNAMES

[FuzzyBear9]

Hi,

I have some single field data that consists of a names in the following format:


Example:
Title Initials/First Name Surname
Mr D David Bolton

There are about 12,000 names. Some that have just titles, some with just initials and so on.

PROBLEM: I want to run an update query using Access 97/2000 that will allow me to keep ONLY the Surnames. I have been able to use MID and TRIM to work on the data from left to right, but I have had to run the query several times. Is there an easy way to do this? How can I work from right to left?

Any help would be greatly appreciated, as this is a problem that occurs quite frequently.

 

[KyleS]

The right function will let you work right to left...

what's the SQL you are using? Kyle :

 

[B827]

Sounds like you need a query with the following SQL

SELECT DISTINCT TableName.FieldName... FROM TableName

that should produce a list of unique Surnames. Sandy

 

[FuzzyBear9]

Hi,

Sorry! I was not clear in my post! The title, initial(s) and surname appear in ONE field. Example:

Mr D Bolton

I would like to run an update query to give me just:

Bolton

Is this possible?

 

[THoey]

I can't find it in my books, but for some reason, I think I heard you can use the INSTR with a negative start position and it will start from the right side? If so, start from the end and look for the first " " and get everything after it. Hmmmm... Am I losing it??? Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...