can i do this with an append query?

[CJA]

Hello!

i have 2 tables like this:

table01 ("stock-in" table)

ItemId | ItemDesc | ItemQty
1 item01 2
2 item02 3

table02 ("serial number" table)

serial | ItemId | ItemDesc
1 1 item01
2 1 item01
3 2 item02
4 2 item02
5 2 item02

Imagine a form with the "table01", i want to create a button to append the
information in "table02".
The serial number is not the problem, because this field is an autonumber in
the "table02", the problem is how can i create records in "table02" based on
field "ItemQty" in "table01".
If i have in the "table01" a record with ItemQty=2 i want to append in
"table02" two records. If the next record in "table01" have quant=3 then i
want to create 3 records in "table02".

can i do this with an append query? If i can´t how can i do this?

thanks for helping!

 

[giorgio rancati]

Hi,

Create a support table with a numbers sequence.

Sequence
Item
1
2
3
4
5
...
100
...


and append the record with this query

 

[CJA]

Thanks Giorgio, works ok but i get this:

serial ItemId ItemDesc
1 1 item01
2 2 item02
3 1 item01
4 2 item02
5 2 item02

Is there any possibility of get item01 with serial 1,2 and item02 with
serial 3,4,5 and so on?

Thanks for help!

 

[giorgio rancati]

Hi,

add the *Order By*
----
INSERT INTO Table02 (ItemId,ItemDesc)
SELECT ItemId,ItemDesc
FROM Table01,Sequence
WHERE Sequence.Item<=Table01.ItemQty
ORDER BY ItemId

 

[CJA]

Perfect!!!, Problem resolved! Thank you!!

Hi,

add the *Order By*
----
INSERT INTO Table02 (ItemId,ItemDesc)
SELECT ItemId,ItemDesc
FROM Table01,Sequence
WHERE Sequence.Item<=Table01.ItemQty
ORDER BY ItemId