Results 1 to 5 of 5
  1. #1
    opopanax666 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    27

    Insert x number of rows based on value

    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!

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    run following queries until no more record inserted:
    Code:
    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
     
    ... ...

  3. #3
    opopanax666 is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    27
    OK, so queries are probably not the way to go here (thanks weekend00). Transferring the problem to "Programming"...

  4. #4
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    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.

  5. #5
    opopanax666 is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    27
    Quote Originally Posted by NTC View Post
    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?)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. INSERT query: insert new data only
    By drh in forum Access
    Replies: 2
    Last Post: 04-04-2014, 05:31 PM
  2. Suggest number based on already used numbers
    By Patience in forum Access
    Replies: 3
    Last Post: 06-16-2010, 04:26 AM
  3. Making New Record Number Next Numerical Number
    By jhillbrown in forum Access
    Replies: 1
    Last Post: 03-10-2010, 11:06 AM
  4. creation of reports with fixed number of rows
    By georgia in forum Reports
    Replies: 1
    Last Post: 02-17-2010, 10:40 PM
  5. Replies: 5
    Last Post: 10-08-2009, 05:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums