Insert x number of rows based on value

[opopanax666]

Hi everyone,



Situation: we run a recycling company for household goods. Our shops can order x amount of tables, televisions, etc. from our main stockroom. So the table with order details looks like:

Id OrderNr Product Units
1 1 table 2
2 1 chair 1
3 2 television 3
4 2 radio 1

What I'm looking for now, is a query (or more) that results in:

Id OrderNr Product Code
1 1 table ___
2 1 table ___
3 1 chair ___
4 2 television ___
5 2 television ___
6 2 television ___
7 2 radio ___

so, for all amounts of units ordered of every product of every order a record (I hope this is clear) is inserted in an other table/query...

I found an example for something similar based on dates, but couldn't get it to work, so perhaps someone here could point me in the right direction (my knowledge of Access is basic, but improving ).

Thanks for any feedback!

[weekend00]

run following queries until no more record inserted:

Query1: insert into NewTable select OrderNr, product from yourTable where units>=1
Query2: insert into NewTable select OrderNr, product from yourTable where units>=2
Query3: insert into NewTable select OrderNr, product from yourTable where units>=3
Query4: insert into NewTable select OrderNr, product from yourTable where units>=4
Query5: insert into NewTable select OrderNr, product from yourTable where units>=5
Query6: insert into NewTable select OrderNr, product from yourTable where units>=6
Query7: insert into NewTable select OrderNr, product from yourTable where units>=7
Query8: insert into NewTable select OrderNr, product from yourTable where units>=8
Query9: insert into NewTable select OrderNr, product from yourTable where units>=9
Query10: insert into NewTable select OrderNr, product from yourTable where units>=10
 
... ...

[opopanax666]

OK, so queries are probably not the way to go here (thanks weekend00). Transferring the problem to "Programming"...

[NTC]

Well the proper way by standard db design theory is that your second table is the 'many' table in a 1:many relationship to the first table.

What one should note is that what you have for data in the second table is all duplicate to the first table and not needed. I copy here:

Id OrderNr Product Code
1 1 table ___
2 1 table ___
3 1 chair ___
4 2 television ___
5 2 television ___
6 2 television ___
7 2 radio ___

other than the sequential ID - all the text is identical. What is really needed is the new unique value not yet showing in the _____ space.

So generally your 'many' table has a form - and that form is inserted as a subform into the form of the main table.....and then this way one completes the ____ info as you go. There isn't a compelling reason for the complexity of trying to insert the records in batch mode up front.

Not sure this is the answer you seek, but hopefully something to consider.

[opopanax666]

There isn't a compelling reason for the complexity of trying to insert the records in batch mode up front.

Well, there is, namely: since we are a recycling company, no two items in our inventory are the same, and we need to keep track of the weight of each of our products (again, with no two of our products having the same weight). Now, I already introduced a system where each of the products receives a unique code which, together with its weight, is stored in a table.
My idea was, when the shop orders 2 tables, I would immediately "make" a table which stores as many of the instances of the product as were ordered, so that I could use this table/query as a basis for the form on which the stockroom employee could write down the codes (and would immediately see how many are needed). And since administration would have to make 2 instances ("rows/records") anyway to store the codes, I thought of doing it at the beginning of the cycle... (is this still clear?)