Spliting a record into many records based on its value
Author Message
Spliting a record into many records based on its value

Hi,

The parent data (original Access table) resembles this:

Carton #
401000014
401000083
401000022

The code should make 1000 records based on the right five
or six digits of one of the above numbers. For instance,
401000014 would produce 013001 thru 014000 in another
table. 401000083 produces 082001 thru 083000 records.

Also if the range is entered as 082001-083000 in a form
textbox, the 1000 records need to be made out of the
right and left of the textbox range. Is this workable
with a loop statement?

401000014 creates a table containing 013001, 013002,
013003, 013004...on up to and ending at 014000

401000015 creates 014001, 014002, 014003...up to 015000
control numbers

Thanks,

Hal

Fri, 15 Jul 2005 11:04:53 GMT
Spliting a record into many records based on its value

Quote:

>Hi,

>The parent data (original Access table) resembles this:

>Carton #
>401000014
>401000083
>401000022

>The code should make 1000 records based on the right five
>or six digits of one of the above numbers. For instance,
>401000014 would produce 013001 thru 014000 in another
>table. 401000083 produces 082001 thru 083000 records.

Do you actually need to have a *TABLE* with these apparently redundant
records?

One way you could do this - I'd suggest dynamically in a Query, though
you could make it an Append query if you want - would be to have a
second table (let's call it Numbers) with one Long Integer field N
with values 1 to 1000 (or to as many records as you'll ever want to
create in one go). Create a Cartesian join query by putting your
source table and this number table in a query with *NO* join line;
include a calculated field

[yourbignumber] MOD 1000 + Numbers.N

or, if (as it would appear) your "number" is a Text field,

Val(Right([yourbignumber], 3) + Numbers.N

If you really need the result in a table, just base an Append query on
this query.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Fri, 15 Jul 2005 14:25:31 GMT

 Page 1 of 1 [ 2 post ]

Relevant Pages