Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    ? Suggestions take a Qty and create rows (2000-3)

    I have a table of Materials, Locations and stock levels:

    Material....Location....Qty On Hand
    123456....LocationA......10

    What I am being requested to do is to take this data and list the Material - Location with a QTY of 1 10 times (or 1 row of data time the qty on hand), so my output should look like:

    Material....Location....Qty On Hand
    123456....LocationA......1
    123456....LocationA......1
    123456....LocationA......1
    123456....LocationA......1
    123456....LocationA......1
    123456....LocationA......1
    123456....LocationA......1
    123456....LocationA......1
    123456....LocationA......1
    123456....LocationA......1

    I am looking to see if anyone has any suggestions on how I would code a query for this. I was thinking some sort of loop repeating the first 2 values and a incremental loop counter (do while loopcntr <= Qty On Hand)

    TIA!
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ? Suggestions take a Qty and create rows (2000-3)

    Is the purpose

    a) to replace the existing record with 10 records in the same table?

    [img]/forums/images/smilies/cool.gif[/img] to create 10 records in another table, linked to the materials table?

    c) Display the data this way in a report (for printing)

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ? Suggestions take a Qty and create rows (2000-3)

    Due to the file size, it would be output to a make table query. The requester wants it in Excel until I made them aware that the final output will be a few 100K rows.

    What I was thinking would be to first do a select query as I will have some criteria not to list every material and sort that by Material - Location.

    Then have the 2nd query or custom function have 2 loops.

    The first one would be a do while current part-location = previous part-location
    then inside of that have a loop counter variable creating a row of output while loopcounter >= Qty On hand

    or maybe an append query that will append the same data elements X times where X = Qty On Hand for that Part-Location

    Just not sure if this is the most efficient
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ? Suggestions take a Qty and create rows (2000-3)

    You'd create the records in code, not in a query, but are you sure this is a good idea? What are you going to do with those hundreds of thousands of records?

  5. #5
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ? Suggestions take a Qty and create rows (2000-3)

    I do as I am told! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    I am not sure why they want the data in this format, but I did verify that this is what they are asking for.
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ? Suggestions take a Qty and create rows (2000-3)

    I still think it would be worth while to try and find out what "they" think they are going to do with all those records.

    Here is a way to do it:
    - Copy your table to the clipboard, then paste it, and specify that you only want the structure, not structure and records.
    - In the following, I have named the original table tblMaterials, and the empty copy tblMaterialsWithIncredibleBloat, just for illustration purposes.
    - Activate the Modules tab of the database window.
    - Click New.
    - Copy the following code into the module window:

    Sub CreateRecords()
    Dim cnn As ADODB.Connection
    Dim rstIn As ADODB.Recordset
    Dim rstOut As ADODB.Recordset
    Dim i As Long

    On Error GoTo ErrHandler

    Set cnn = CurrentProject.Connection
    rstIn.Open "tblMaterials", cnn, adOpenForwardOnly, adLockOptimistic, adCmdTableDirect
    rstOut.Open "tblMaterialsWithIncredibleBloat", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    Do While Not rstIn.EOF
    For i = 1 To Nz(rstIn!<!t>[Qty On Hand]<!/t>, 0)
    rstOut.AddNew
    rstOut!Material = rstIn!Material
    rstOut!Location = rstIn!Location
    rstOut!<!t>[Qty On Hand]<!/t> = 1
    rstOut.Update
    Next i
    rstIn.MoveNext
    Loop

    ExitHandler:
    On Error Resume Next
    rstOut.Close
    rstIn.Close
    Set rstOut = Nothing
    Set rstIn = Nothing
    Set cnn = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    - Substitute the correct table names, and if necessary, field names.
    - Run the code by clicking in it and pressing F5.
    - Go get a cup of coffee.

  7. #7
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ? Suggestions take a Qty and create rows (2000-3)

    Thanks for the code example. I need to see what is missing as I am getting "user-defined type not defined" on the ADODB Dim statements.

    (guess I am missing an object reference somewhere)

    The request is for some statistical analysis for random sampling of materials as part of a "crystal ball" analysys.

    If the part location is only listed once for the total qty, that total qty only has 1 chance to be selected. (so in my db example, material 123456 would only have 1 change of being selected in 75K records and only 1 of the QTY of 10 would be analysed)
    By separating the qty on separate lines, this code could now be possibly be selected from 0 - 10 times in the total universe instead of 0 - 1 for the total qty on hand.

    This code is going to create a table of almost 700K rows. At least it is not 7 million. <img src=/S/angel.gif border=0 alt=angel width=15 height=21>
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  8. #8
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ? Suggestions take a Qty and create rows (2000-3)

    Looks like I was missing some Active X references. Now I am getting :"Object variable or With block variable not set"

    Here is the code I am using:
    Sub CreateRecords()
    Dim cnn As ADODB.Connection
    Dim rstIn As ADODB.Recordset
    Dim rstOut As ADODB.Recordset
    Dim i As Long

    On Error GoTo ErrHandler

    Set cnn = CurrentProject.Connection
    rstIn.Open "Mard_SingleLine_Start_Table", cnn, adOpenForwardOnly, adLockOptimistic, adCmdTableDirect
    rstOut.Open "Mard_SingleLine_End_Table", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    Do While Not rstIn.EOF
    For i = 1 To Nz(rstIn!QtyOH, 0)
    rstOut.AddNew
    rstOut!Material = rstIn!Material
    rstOut!Description = rstIn!Description
    rstOut!Plant = rstIn!Plant
    rstOut!sloc = rstIn!sloc
    rstOut!Loc_Type = rstIn!Loc_Type
    rstOut!QtyOH = 1
    rstOut.Update
    Next i
    rstIn.MoveNext
    Loop

    ExitHandler:
    On Error Resume Next
    rstOut.Close
    rstIn.Close
    Set rstOut = Nothing
    Set rstIn = Nothing
    Set cnn = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    I used a make table query to start with the required sub-set of materials to create the bloat table with named Mard_SingleLine_Start_Table
    Copied the structure of this to a new table Mard_SingleLine_End_Table

    Both have the same structure:
    Material - Text
    Description - Text
    Plant - Text
    sloc - text
    QtyOH - Number - Double
    Loc_Type - text


    When I step through this using F8, it jumps from the rstIn.Open line down to the error handler. I did a F2 on the table name to copy it as is and pasted in between the " " to avoid any typos, so I am not sure what the issue is.

    Any ideas on what I am doing wrong?
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ? Suggestions take a Qty and create rows (2000-3)

    Sorry, my fault. Change the declarations of the recordsets to

    Dim rstIn As New ADODB.Recordset
    Dim rstOut As New ADODB.Recordset

  10. #10
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ? Suggestions take a Qty and create rows (2000-3)

    Thank you. Works like a charm.
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

Posting Permissions

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