Split string into one field, de-concatenated

[Kate]

I have a field ([otherstaff]) in a table (tblCompanies]),
containing 0 to several staff names, separated by a comma.
I'm trying to create a query which will display all of the
individual names, but in one column, not as many as there
are names per field.

For example, if a record contains "May Jones, John Way,
Steve Bright" I would like to have the resulting query show:

May Jones
John Way
Steve Bright


in separate rows.

I've created the following code using the Split function,
but it only seems to return the first name in a field. I'm
wondering if there's a way to have it return all the names.
I'm calling the function in a query such as this:

SELECT DISTINCTROW splitstaff([otherstaff]) AS Who
FROM tblCompanies
WHERE (((tblCompanies.OtherStaff) Is Not Null And
(tblCompanies.OtherStaff)<>""));

This is the splitstaff function:

Public Function splitStaff(strFld As String)
'creates an array of names of other staff, parsed out of the
'otherstaff' field in tblcompanies
'used in the query 'otherstaff' as a function
Dim strParse() As String, strValue As String
Dim k As Integer

On Error Resume Next
strParse = Split(strFld, ",")
For k = 0 To UBound(strParse)
splitStaff = LTrim(strParse(k))
Next k
End Function

 

[Rick B]

Your data structure is not following normalized rules. If you have a field
that could contain one entry, or multiple entries, then it needs to be a
one-to-many relationship in a separate field. You have seen why this rule
is so important in trying to work out the problem you present here.

You should correct your database structure and move these entries into a new
table.

 

[Kate]

Rick, I'm aware that it's not normalized this way, BUT there
are times when it's best for the
usability of the database to break the rules. I won't go
into the details here, but the question still remains, is it
possible to do what I have asked?

-Kate

 

[Kate]

More info: the function I've created returns only the LAST
name in the string.

 

[John Vinson]

For example, if a record contains "May Jones, John Way,
Steve Bright" I would like to have the resulting query show:

May Jones
John Way
Steve Bright


in separate rows.

I have to agree with the normalization suggestion - if you need to
search for all records with a particular value of OtherStaff, you
would find it much easier using a normalized table (not to mention the
ability to use a combo or listbox to select names rather than typing
them in and risking spelling errors. But see below...

I've created the following code using the Split function,
but it only seems to return the first name in a field. I'm
wondering if there's a way to have it return all the names.

It's returning all the names... and you're then throwing all but the
last one away.

I'm calling the function in a query such as this:

SELECT DISTINCTROW splitstaff([otherstaff]) AS Who
FROM tblCompanies
WHERE (((tblCompanies.OtherStaff) Is Not Null And
(tblCompanies.OtherStaff)<>""));

This is the splitstaff function:

Public Function splitStaff(strFld As String)
'creates an array of names of other staff, parsed out of the
'otherstaff' field in tblcompanies
'used in the query 'otherstaff' as a function
Dim strParse() As String, strValue As String
Dim k As Integer

Add a line

splitStaff = ""

to initialize...

On Error Resume Next
strParse = Split(strFld, ",")
For k = 0 To UBound(strParse)
splitStaff = LTrim(strParse(k))

Change this to

splitStaff = splitStaff & vbNewLine & LTrim(strParse(k))

Next k
End Function

In the Form or Report in which you display this query, be sure to size
the textbox to allow multiple lines; on a Report you can set its Can
Shrink/Can Grow properties to true.

John W. Vinson[MVP]

 

[Kate]

Thanks, John, for addressing the issue even though you
disagree with the design!
The reason why I've done this is, I have two fields in the
table; one for the primary person
involved in completing a task, and another (this one) for
other staff who were involved.

I have them both in the same table because I have a form in
which I have placed a number
of comboboxes which serve as additive filters on the table
of tasks. One of these filters contains all the names of
staff. It then filters the table on the name selected, be
it in the primary staff field or the 'other staff' field. I
tried doing this with the otherstaff placed in a separate
table, normalized form, but it became waaaayyy too
complicated in the filter procedure to apply the filter.

The solution you've given below won't work, because I need
the names to be discrete, not part of a group separated by
line breaks. What I've had to do is modify the splitstaff
function to include the position of the string within the
string as a parameter. This is then called from several
queries, incrementing the integer until no more names are
returned. The queries are unioned to produce the final list.

I CAN get all the names in an array within a function, but
don't know how to use that array as part of an SQL statement
to set the combobox properties. A variation that worked but
seemed 'inelegant' was to create a table to hold the array
of staff names, and repopulate it each time the form was
updated.

Kate

For example, if a record contains "May Jones, John Way,
Steve Bright" I would like to have the resulting query show:

May Jones
John Way
Steve Bright


in separate rows.

I have to agree with the normalization suggestion - if you need to
search for all records with a particular value of OtherStaff, you
would find it much easier using a normalized table (not to mention the
ability to use a combo or listbox to select names rather than typing
them in and risking spelling errors. But see below...

I've created the following code using the Split function,
but it only seems to return the first name in a field. I'm
wondering if there's a way to have it return all the names.

It's returning all the names... and you're then throwing all but the
last one away.

I'm calling the function in a query such as this:

SELECT DISTINCTROW splitstaff([otherstaff]) AS Who
FROM tblCompanies
WHERE (((tblCompanies.OtherStaff) Is Not Null And
(tblCompanies.OtherStaff)<>""));

This is the splitstaff function:

Public Function splitStaff(strFld As String)
'creates an array of names of other staff, parsed out of the
'otherstaff' field in tblcompanies
'used in the query 'otherstaff' as a function
Dim strParse() As String, strValue As String
Dim k As Integer

Add a line

splitStaff = ""

to initialize...

On Error Resume Next
strParse = Split(strFld, ",")
For k = 0 To UBound(strParse)
splitStaff = LTrim(strParse(k))

Change this to

splitStaff = splitStaff & vbNewLine & LTrim(strParse(k))

Next k
End Function

In the Form or Report in which you display this query, be sure to size
the textbox to allow multiple lines; on a Report you can set its Can
Shrink/Can Grow properties to true.

John W. Vinson[MVP]

 

[John Vinson]

I CAN get all the names in an array within a function, but
don't know how to use that array as part of an SQL statement
to set the combobox properties.

You could set the Combo's RowSourceType property to Value List, and
the RowSource to a text string

"Joe Doakes";"Fred Flintstone";"Irma McPhee"

Of course it's dead easy with a normalized table (just use the
employeeID and name in a Query).

I'd suggest that you reopen the issue of using a combo box (again,
rather than typing the names into a non-atomic denormalized field) in
your query. What was the obstacle to searching? I'd expect that a
Query joining the project table to the additional-staff table, and
thence to the Employees table to pick up the name, should be pretty
straightforward!

John W. Vinson[MVP]

 

[Kate]

John, thanks for staying with me. It wasn't easy at all, in
fact, very complicated I found. This is why:

I am controlling the filters placed on the main form, of
tasks completed by staff.
I am rebuilding the form's filter every time either:

1. a single filter box is cleared of its value, or all
filters are removed
2. a value is selected in a filter box
3. the form's data are updated by either addition or
deletion of records, or changing of existing records

The Staff filter box is one of nine such boxes. As filters
are added or removed, the available values in each list
change to reflect the filtered recordset. Also, the form's
recordset becomes filtered in the same way.

All of the filter boxes derive their values from the form's
recordset, which is one table. When I attempted to
restructure the db so that the 'otherstaff' were in a
separate table, and then applied a filter to the recordset
that searched for a name in EITHER the main table's
completedby person OR the otherstaff table's list of many
staff to one task, it got too complex. This is what the
main form's filter looked like at one point:

" INNER JOIN AllStaff ON tblcompanies.R_ID = AllStaff.R_id
where AllStaff.CompletedBy = 'Mary Jones')

Well, obviously that's not a valid filter. The main form's
recordsource could no longer one table, filtered, but would
at times be a join between that table and the staff table,
limited by the staff, and yet I still wanted the form to
display the filtered tasks in their entirely, including all
of the other staff that worked on the task within a subform!

Okay, this is becoming too hard to explain. Thanks for
staying with me so far. I just wish I could get that array
of names usable in a query!

 

[John Vinson]

Okay, this is becoming too hard to explain. Thanks for
staying with me so far. I just wish I could get that array
of names usable in a query!

How about a query criterion of

[otherstaff] LIKE "*" & <value from the combo> & "*"


John W. Vinson[MVP]

 

[Kate]

This is the filter that is created when the staff filter box
is used:

"(CompletedBy = '" + Me![dspCompletedby].Column(0) + "' OR
OtherStaff like '*" + Me![dspCompletedby].Column(0) + "*')"

I gotta go. Thanks for your help; I guess there's just
really no 'elegant' way to be de-normalized!

Okay, this is becoming too hard to explain. Thanks for
staying with me so far. I just wish I could get that array
of names usable in a query!

How about a query criterion of

[otherstaff] LIKE "*" & <value from the combo> & "*"


John W. Vinson[MVP]