split up a field (1 Viewer)

[lipin]

I am trying to compare data from two different systems. I need the employee id, name, and hours worked. The prolblem is that in one system the name and id number are separete fileds. In the other, they are merged into i field where the name is 6 letters of last name, then first initial then 4 digit id number like so:

ARMSTR K 1234
CLARK A 3245

So is there a way to split this into 2 cloumns? Maybe cut off the last 4 characters(the Empl id#)and store it in a new field?

Any help is appreciated. Thank you.

 

[TessB]

Use right(fieldname, 4) AS NewFieldName to extract your id into a new column. But then you would have to take off the last 4 digits for your name column. Are you sure you want to do it that way?

Personally, I would just do it backwards... convert the 2 fields into a new merged info field to match the existing one.


[This message has been edited by TessB (edited 06-03-2002).]

 

[lipin]

Well I thought of that but I thought it would be more difficult because in system 1
the names/id# looke like this IN TWO fields

KENNETH ARMSTRONG 1234
AMY CLARK 3245

and system 2 they are in ONE filed as:

ARMSTR K 1234
CLARK A

so I thought that the easiest thing would be cut off the last four(the emp id) because the fact that the last 4 characters are always the id number. So in my union query and the total query, that run off these tables, the total can be grouped be employee and the only thing that has to match is the id number, since the names are all different lengths.
If you combine the two separate ones, the last name and the first initial will have to be exactly in the same format as where they are already combined. That seemed a lot more difficult to me.

 

[TessB]

In that case, you are absolutely right. I would just use the Right(fieldname, 4) code to extract it then.

 

[lipin]

This probably sounds stupid but Where do I put that??