Re: looping thru a fields values

• Previous message: Ernie: "I am sure this is simple BUT. . . . Deduping a table - Distinct . . . But want o"
• In reply to: Rob: "looping thru a fields values"
• Messages sorted by: [ date ] [ thread ]

Date: Tue, 20 Jul 2004 12:04:05 +0800

The simplest way might be to write a piece of code using the Split()
function to generate an array of FirstNames and an array of LastNames. Loop
from LBound to UBound, and AddNew to a Recordset (DAO) opened into the
target table for each one.

Instr() has a Start argument that you can use to tell it to begin looking
after the comma you have already parsed. You could try that in an Append
query, but I suspect it would be more difficult than using Split() in code,
especially if the number of names in different rows varies.

Is there any chance of people being duplicated here? i.e. could one person
be associated with multiple rows? Ultimately, it would seem that you need
some kind of ClientID table with only one record for each person, and the
table you are trying to build would then store the ClientID rather than the
names.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Rob" <bccamper@hotpop.com> wrote in message
news:042501c46e0c$2754fa80$a501280a@phx.gbl...
> I am not exactly new to Access but I am trying something
> that is a little over my head. I have a form where I
> input a series of first names in a field called FirstName
> seperated by commas (eg Rob, Jim, Susan), and a series of
> last names entered in a field called LastName seperated
> by commas (eg Guest, Everett, Wills). I want to match the
> first, second, etc names in each field and for each pair
> insert a record into a different table (eg record 1 - Rob
> Guest, record 2 - Jim Everett, record 3 - Susan Wills).
> How should I do this? Should I be using an append query,
> a macro, or do i need to use VBA to do it. I tried an
> append query but could only use left and instr to get the
> first name.

• Previous message: Ernie: "I am sure this is simple BUT. . . . Deduping a table - Distinct . . . But want o"
• In reply to: Rob: "looping thru a fields values"
• Messages sorted by: [ date ] [ thread ]