Data Breakout in Access SQL

[artemetis]

in tblMain I have a field that contains txt in the following format:

"firstname lastname (misc)"

Not all entries have the (misc).

I need to break out the three fields

firstname, lastname, and other when included in the entry.

Thanks

[twinnyfo]

How do you want to break out this data? In a new table?

One way to do it is to create the new table with the fields you want, then, in VBA create a recordset based on the old table, and cycle through each string, looking for the space characters (" "), then split up the string into the Left, middle and right (if any) portions and save those substrings to the new table.

This should point you in the right direction. Let us know where you might need additional help.

[NeoPa]

This is a perfect illustration of why you never store combined data in a single field. These values should be stored in three separate fields and put together when required.

Nevertheless, you've asked about splitting them so we'd better deal with that. Not so easy as you haven't explained where you want the data broken out to or even exactly the logic which determines which point at which to break the data (Is it by the first and second spaces? Are the parentheses a more reliable indicator?). All these are important points that need to be included in a question if you actually want a usable answer. In very simple terms you can use the Split() function in VBA to produce an array of items split by the spaces. This may help you, but I suspect you need more than that. If so, let us know the full question so we can help.

[artemetis]

Thanks for the reply, folks. Exactly, Neo...perfect illustration. I'm just a dabbler and don't get to use these programmings function as often as I would like, but I am sorry for not being more clear.

the original table has the fields:
uid, person_info

uid is autonumber and person_info is "firstname lastname (misc)", a horrible piece of data.

I would like to run a make table query to dump these informations as follows:
uid
pFirstName
pLastName
pCoded

The parenthesis aren't a delimiter, but actually in the text field and the problem is, is that not all line items have the (misc)in there entry, making a trim by space make some functions errors.

I am once and again lost.

thanks for all the help!

[NeoPa]

What you must do for yourself, before we even get involved, is to decide exactly what logic should be used to separate out the sub-fields from the existing data. This cannot be done on a per-record basis unless you want to get a human to do it. A computer needs a clear and defined understanding of what it is that is required before it can work for you.

This is essentially the heart of the problem - The definition. Without it we cannot help you to implement it in code (apart from very general instructions that won't actually be very helpful).

Having said that - once you do have the logic clearly defined, then implementing it shouldn't be too difficult.

[artemetis]

Thanks, NeoPa.

I'm looking to get the following. Ihope this next information helps.

Easiest is to just display the following in a query.

Each record looks like this from tblMaster: uid, person_info (uid not shown in results)

arthur smith (18_7)
josephy and (87_3)
patrick murphy (8_e3/2)
ed sully
mark vignion
mark jalus (7_3_0)

I would like that data from these entries to be displayed as follows, in four separate fields:

uid, pFirstName, pLastName, *pCoded

*pCoded - null if none in string

I'm hoping this makes a little more sense.

Thanks you agian

[twinnyfo]

Using either the Split() function, as described by NeoPa, or evaluating each string character by character should work. As long as every record has FirstName and LastName and some records have the pCoded information, you should be ok.

I would also recommend you not use a "*" in the field name (unless the asterisk was just alerting us to a detail about the field...).

[artemetis]

Thanks for the reply. I'm not using an asterisk in the field name; that was just for show that additional information for this field. like "See below"

Neo is right with split function, I am just not so sure on how to use this.

There is a first space from left, after first name. There is last name after this, then space before parentheses on some records.

[NeoPa]

Artemetis:
There is a first space from left, after first name. There is last name after this, then space before parentheses on some records.

Bingo! This is what we were asking for. What you needed to provide. What you need to understand yourself. Example data isn't a specification. It's simply some example data.

You don't specify exactly whether the third space or the open parenthesis should be used to recognise the pCoded value so we'll assume ' (' is required.

Unfortunately (as the Split() function is a very good way to handle this), the Split() function works only in VBA and not in SQL. It appears that an APPEND query is required here. As is sometimes the way in SQL, the code is somewhat kludgy when compared to VBA.

INSERT INTO [NewTable]
     ( [UID]
     , [pFirstName]
     , [pLastName]
     , [pCoded] )
SELECT [UID]
     , Left([Person_Info],[Space1]-1) AS [pFirstName]
     , Mid([Person_Info],[Space1]+1,IIf([Space2]=0,Len([Person_Info]),[Space2]-1)-[Space1]) AS [pLastName]
     , IIf([Space2]=0,Null,Replace(Mid([Person_Info],[Space2]+2,99),')','')) AS [pCoded] )
FROM   (SELECT [UID]
             , [Person_Info]
             , InStr(1,[Person_Info],' ') AS [Space1]
             , InStr([LenFN]+2,[Person_Info],' (')-1 AS [Space2]
        FROM   [tblMaster]) AS [qM]

[artemetis]

Thank you!
I've added this to access query; first it kicks back Enter Parameter Value "LenFN" box. I leave it blank and it displays uid and pFirstName. pLastName and pCoded display #Error using this code

SELECT qM.[UID], Left([Person_Info],[Space1]-1) AS pFirstName, Mid([Person_Info],[Space1]+1,IIf([Space2]=0,Len([Person_Info]),[Space2]-1)-[Space1]) AS pLastName, IIf([Space2]=0,Null,Replace(Mid([Person_Info],[Space2]+2,99),')','')) AS pCoded
FROM (SELECT [UID], [Person_Info], InStr(1,[Person_Info],' ') AS Space1, InStr([LenFN]+2,[Person_Info],' (')-1 AS Space2 FROM tblMain)  AS qM;

[twinnyfo]

Try replacing "[LenFN]+2" in your SQL with "[Space1]+1".

I don't think LenFN was defined in the query, but I may be mistaken.

[NeoPa]

Artemetis:
I've added this to access query

Wouldn't it make more sense to add what I suggested? You've changed much of what I wrote and I can see no reason for it. It's also formatted poorly so I can't even read it without much effort.

Excuse me, but I'm confused by that.