Making many rows in one table out of one row from another table

  • Thread starter Thread starter David
  • Start date Start date
D

David

I have one table that has a quantity value in it. I need this quantity to
have its own row in another table that is autonumbered. If the quantity was
5 I would need 5 rows with a few columns of data pulled into the other
table. How can I make access autogenerate x amount of rows in another
table?
 
David said:
I have one table that has a quantity value in it. I need this
quantity to have its own row in another table that is autonumbered. If the
quantity was 5 I would need 5 rows with a few columns of data
pulled into the other table. How can I make access autogenerate x
amount of rows in another table?
Click to expand...

Frankly it sounds like you really want a query combing those two tables.
It is seldom desirable to duplicate data in different tables.

However from the limited information about your table structure and what
you really need, I can't offer any specific advice.
 
I am using an order entry form to enter data about orders we take in from
customers in an order entry table. The data that I need to be concerned
with in the order entry table is the ordernumber field and quantity field.
I want to copy the ordernumber field to the second table and have as many
rows of this data as the quantity of the order. Our customers order
multiple shipments at a time and I need to be able to generate seperate
ticket numbers for each shipment. I wanted to use the autonumber field of
the second table to generate all the ticket numbers, but then have the order
number field to relate back to the original order info.

I hope this makes sense. Databases are a pain to explain.
 
I have one table that has a quantity value in it. I need this quantity to
have its own row in another table that is autonumbered. If the quantity was
5 I would need 5 rows with a few columns of data pulled into the other
table. How can I make access autogenerate x amount of rows in another
table?
Click to expand...

I'd suggest doing it dynamically in a Query rather than storing the
data redundantly in another table - though you could make my
suggestion into an Append query if you have a real need to do so.

Create a little utility table named Num, with one number field N. You
can use code or (more easily) Excel Fill-Sequence to fill it with
values from 0 through the largest quantity you'll ever need (be
generous, it's a small table even with 65536 rows).

Create a Query with your table and Num, with NO join line; put a
criterion on N of

< [Quantity]

This will duplicate the fields from your table [quantity] times, and
you even get a sequential N (for "Package " & [N] + 1 & " of " &
[Quantity] for example) thrown in for free.

John W. Vinson[MVP]
 
Back
Top