I am an old FoxPro programmer having trouble transitioning to Access,
and I have a hot project to work on.

 I have a record for a company.  That record has a field of account
numbers.  The account number field can have from 1 to 16 account
numbers in it, separatd by a ; or semicolon.  (Shades of PICK).

How do I convert this type of record

ABC Co, Main Street, Anywhere, PA, 1234;1237;1239;1290

to this

ABC Co, Main Street, Anywhere, PA, 1234
ABC Co, Main Street, Anywhere, PA, 1237
ABC Co, Main Street, Anywhere, PA, 1239
ABC Co, Main Street, Anywhere, PA, 1290

How do I do it in Access?

Mainly I can't find references to do what I thought were common
database functions, such as 

1. stepping through a table's recrods and

2.  adding a record to a second table based on some sort of processing
logic.

Here is a simpilified version of what I would do if I were to do it in
Foxpro

use Table1

use Table2

select Table1 - change to Table1
do while not EOF()  - end of file

	test for necessary condition to add more records
	do procedure add a record & loop through if necessayr
	
	skip to next record

enddo

procedure add a record to a second table

	do processing
	
end procedure

Any help would be appreciated

On Sun, 29 Jun 2008 13:11:39 -0400, JF Main <jf@yahoo.com> wrote:

>I am an old FoxPro programmer having trouble transitioning to Access,
>and I have a hot project to work on.
>
> I have a record for a company.  That record has a field of account
>numbers.  The account number field can have from 1 to 16 account
>numbers in it, separatd by a ; or semicolon.  (Shades of PICK).
>
>How do I convert this type of record
>
>ABC Co, Main Street, Anywhere, PA, 1234;1237;1239;1290
>
>to this
>
>ABC Co, Main Street, Anywhere, PA, 1234
>ABC Co, Main Street, Anywhere, PA, 1237
>ABC Co, Main Street, Anywhere, PA, 1239
>ABC Co, Main Street, Anywhere, PA, 1290
>
>How do I do it in Access?
>

Is that structure part of your database design?  If so, it desperately needs to
be normalized.  The company name, address, city, state, zip, account numbers,
etc., need to be in separate fields.  If it's for a report, it would still
benefit from normalization.

Please answer a few questions before I start writing code.  

(1) Is it possible to separate all account numbers by semicolons, e.g., 
ABC Co, Main Street, Anywhere, PA;1234;1237;1239;1290
so that the state is delimited by a semicolon as well?  Would make parsing the
string a LOT easier.

(2) What's the source of the input records?  (Foxbase table/text file/etc)

(3) What's the destination of the parsed records? (Access table(s)/text
file/etc)  If Access table(s), is it possible to alter the table structure?  A
couple of related tables would be appropriate here..  a Company table and an
Account table.  

==============

Dave M

Never take a laxative and a sleeping pill at the same time!!