expand total qty into rows

[JIM.H.]

Hello,
I have a table with two fields: "id, qty". qty can be
negative, I need to sum all qty for each id, if result is
positive, let us say 3, I need to create three rows: "id,
1" in the table to show that id appears three time. If
result is negative or zero I should not add any row. How
can I do that?
Jim.

 

[John Vinson]

Hello,
I have a table with two fields: "id, qty". qty can be
negative, I need to sum all qty for each id, if result is
positive, let us say 3, I need to create three rows: "id,
1" in the table to show that id appears three time. If
result is negative or zero I should not add any row. How
can I do that?
Jim.

Hm. Tricky!

I'd suggest creating a useful little table named NUM, with one Long
Integer field N. Fill it with values N from 1 to 10000 or so (go to
Excel and fill down is a quick way to do so).

Create a Query by adding your table and NUM to the query grid.
Initially join N to qty. Then go into SQL view and edit it to

SELECT table.ID, (1) AS SplitQty
FROM table INNER JOIN Num
ON table.qty >= Num.N
ORDER BY ID;

I would say there is very little benefit in storing these rows in your
table (or any other table)!