Query to search for data all in caps

[Guest]

Hi I am trying to fix a database I have inherited in a new job.
one of the tables is called phone
now the problem with it is that one of the feilds "name/position'
contains way to much information for one feild
it holds FirstName, LastName (seperated by a comma and a space) then it also
holds the Position (All in caps but only seperated by a space from LastName)
The position varies in the number of words some are 2 words some are 5
to futher complicate things some have more then 2 names first last and
another if they have two last names. All of this is held in one feild and
scares me.
does anyone know how I could set up a query to seperated this into several
different feilds ie FirstName, LastName, Other, Position?
Thanks
oh and its Access 2000 on xp

 

[Guest]

it holds FirstName, LastName (seperated by a comma and a space)
Don't you mean last, first separated by a comma?
I think you will need several queries and then some manual data review.

 

[Douglas J. Steele]

Realistically, you're going to need VBA to do this. You'll need to write a
function that accepts the name/position data and returns the different parts
of the address. And, of course, since a function can only return a single
value, you'll actually need one function for each field you want returned
(one to return FirstName, one to return LastName, one to return Position and
so on)

It's far too complicated to do using just SQL: you'd need far too many
different subqueries.

 

[Guest]

sorry yes I did mean LastName Firstname

Don't you mean last, first separated by a comma?
I think you will need several queries and then some manual data review.

 

[Guest]

You copy the table/field into Excel and the go to Data-Text to Columns. You
can tell it to separate the field by commas. Then you can import that back
into your new table with correct field names.

 

[Dale Fye]

Steve,

I empathize with you. There are so many different combinations of ways to
store just the name, let alone a multi-word position description. No matter
how you do this, you are going to end up having to review every record
manually. I find the only field that is harder to parse than a name field
is an address field with the #, street, city, state, and zip all included.

A while ago, I wrote a short program at work to do something similiar with
data I was getting from an Excel Spreadsheet.
1. In my table, I created all of the appropriate fields, along with the
ugly one.
2. I then created a form that had all of these fields in it.
3. I created a function in a code module that would take the selected text
and put it in the appropriate field(First Name, Last Name, Middle Name,
Prefix, Suffix, and Position). This allowed me to highlight a section of
text and with a right click and a left click copy everything that was
highlighted to a specific field. This prevented me from having to drag and
drop or cut and paste). You could modify this so that if the selLength = 0
that it would select all the characters to the right of the SelStart
position. This function looked somewhat like:

Public Function ParsePosition(strField As String) As String

Dim ctrl As TextBox
Dim strSQL As String
Dim strValue As String

Set ctrl = Screen.ActiveControl

If Len(ctrl.Text & "") = 0 Then Exit Function
If ctrl.SelLength = 0 Then Exit Function
Screen.ActiveForm.Controls(strField) = REPLACE(Trim(ctrl.SelText), ",",
"")

End Function

4. I created a new popup menu (easier than it seems so if you decide you
want to go this route, post back) and added items to the menu for each of
the functions I created during step 3. Then I changed the ShortcutMenu
property of the form to use this new popup menu.

Now all you have to do is open the form, highlight a section of the text,
right click to get the popup menu, then select the field you want to move
the text to. This will be time consuming, but it is easier than doing a cut
and paste. You could start out trying some of the techniques mentioned
earlier in the thread and then use this form to perform review the data.

Good luck

HTH
Dale