Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Posts
    2

    Unanswered: Creating duplicate records

    Hi there,

    I have a table that could display information like this:

    Code Quantity
    1234 2
    4321 1
    5678 3

    Basically I want to use a query to creat a new table with the records duplicated the amount of times in the quantity field. so the new table based on the info above would look like this:

    Code Quantity
    1234 2
    1234 2
    4321 1
    5678 3
    5678 3
    5678 3

    Anjy ideas? I presume I'm gonna have to create a loop in VBA or something.
    Cheers.

  2. #2
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Interesting.

    You need two recordsets. The first one is for your products table the second one is your updateable one:

    (warning, untested code follows):

    With rst
    .movefirst
    while not .eof
    for i = 1 to .fields("quantity")
    with updatableRST
    .addnew
    .fields("product") = rst.fields("product")
    .fields("quantity") = rst.fields("quantity")
    .update
    end with
    next i
    wend
    end with

  3. #3
    Join Date
    Jul 2006
    Posts
    2
    Cheers for that - will test on Monday.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    another method employs the ever useful integers table

    (if you don't have an integers table yet, make one -- it has a single column called i, which is the primary key, with values 0 through 9)

    then use the integers table to generate your rows
    Code:
      select Code
           , Quantity
        from yourtable, integers
       where i between 1 and Quantity
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •