Loop in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Each record in this table is a combination of several strings. Each string
contains a user name. I need to extract the user names from these strings.
I know how to extract it form one string but I don't know how to make it
repeat the action (with a loop or something) to get the other user names.
There could be between one and 350 user names in a string. How can I Extract
these names?

Thanks
 
A query repeats the same action for each record. I do not see your need for
a loop. What is your SQL that works for one record and not for all?
 
Niles,

If you mean that a record looks like 'Name1, Name2, Name3, ...' or something
similar and you want to get a list consisting of 'Name1', 'Name2' etc., then
I think the answer is that using SQL alone, you can't. You're going to need
some code to do this.

If the names are separated by say commas, then in VB you can write a
procedure that splits the names in a row out into an array using the split
function, along the lines of:

dim nameArray() as string
nameArray = split (currentRecord, ",")

You would of course need the appropriate code to get the records from the
table.

If the names are simply listed with no discernible pattern or separator,
then you're out of luck. To automate this, you've got to have a way of
reliably identifying what you want. Even if you split a space-separated set
of names on spaces, how would you know what the name is? If every single name
is first name, last name you can still get away with it. But if, for example,
someone has a double last name (Van Horn, eg), or there are middle initials
sometimes, you are back to having no pattern to work on.

Free text names are a notoriously hard problem.

Good Luck!
 
The record has several strings that are separated by a '^' character. Within
each string there are commas and other characters that allow me to search for
the names using instr(), left() and Right() functions
Can use the Array function in the query design mode? what other code would I
need to pull the info from these strings?
Thanks
 
Ok... build a query and do the loops using a macro that calls itself.

Use an append query and have a field that notes where the name was found in
the record using InStr. Use the record identifier and last location plus one
for your criteria.
 
Back
Top