Multiple copying of rows in a table

[LeifT]

Let´s say I have a table with 1 row and 4 fields - F1,F2,F3 and F4.
If I now want to copy this fields from a form where I for example have a
field with the title "How many copies ?" and when I put in the digit "4" in
this field I want to have a new table with 1 field where I have
F1
F1
F1
F1
F2
F2
F2
F3
F3
F4
i.e. 4 rows of F1,3 rows of F2,2 rows of F3 and 1 row of F4.

How is the solution for this problem ??

 

[John Vinson]

Let´s say I have a table with 1 row and 4 fields - F1,F2,F3 and F4.
If I now want to copy this fields from a form where I for example have a
field with the title "How many copies ?" and when I put in the digit "4" in
this field I want to have a new table with 1 field where I have
F1
F1
F1
F1
F2
F2
F2
F3
F3
F4
i.e. 4 rows of F1,3 rows of F2,2 rows of F3 and 1 row of F4.

How is the solution for this problem ??

I would suggest that you almost certainly do NOT want to store this
information redundantly in a new table! You can instead use a Query
for just about anything you would use a Table for - reports, labels,
exports, etc.

One way to do so is to create a table named Num with one Integer field
N; use Excel fill-down to fill it with values from 0 through the
largest number of copies you'll ever want (be generous, I often use
10000 rows).

Create a Query based on your table. Add Num to the query with NO join
line. Then go into the SQL window and edit it into a UNION query:

SELECT yourtable.F1
FROM yourtable, Num
WHERE N < [Forms]![YourForm]![HowMany]
UNION ALL
SELECT yourtable.F2
FROM yourtable, Num
WHERE N < [Forms]![YourForm]![HowMany]
UNION ALL
SELECT yourtable.F3
FROM yourtable, Num
WHERE N < [Forms]![YourForm]![HowMany]
UNION ALL
SELECT yourtable.F4
FROM yourtable, Num
WHERE N < [Forms]![YourForm]![HowMany]


Note that the need to do this strongly suggests that your table
normalization is questionable...!

John W. Vinson[MVP]

 

[LeifT]

Thank You for Your answer

I'll try but I think it will work
--
LeifT


"John Vinson" skrev:

Let´s say I have a table with 1 row and 4 fields - F1,F2,F3 and F4.
If I now want to copy this fields from a form where I for example have a
field with the title "How many copies ?" and when I put in the digit "4" in
this field I want to have a new table with 1 field where I have
F1
F1
F1
F1
F2
F2
F2
F3
F3
F4
i.e. 4 rows of F1,3 rows of F2,2 rows of F3 and 1 row of F4.

How is the solution for this problem ??

I would suggest that you almost certainly do NOT want to store this
information redundantly in a new table! You can instead use a Query
for just about anything you would use a Table for - reports, labels,
exports, etc.

One way to do so is to create a table named Num with one Integer field
N; use Excel fill-down to fill it with values from 0 through the
largest number of copies you'll ever want (be generous, I often use
10000 rows).

Create a Query based on your table. Add Num to the query with NO join
line. Then go into the SQL window and edit it into a UNION query:

SELECT yourtable.F1
FROM yourtable, Num
WHERE N < [Forms]![YourForm]![HowMany]
UNION ALL
SELECT yourtable.F2
FROM yourtable, Num
WHERE N < [Forms]![YourForm]![HowMany]
UNION ALL
SELECT yourtable.F3
FROM yourtable, Num
WHERE N < [Forms]![YourForm]![HowMany]
UNION ALL
SELECT yourtable.F4
FROM yourtable, Num
WHERE N < [Forms]![YourForm]![HowMany]


Note that the need to do this strongly suggests that your table
normalization is questionable...!

John W. Vinson[MVP]