Query help.

[Mazkot]

I would like to know if it's possible to create duplicate records based on a field in original table.   Below is a simple look at what I want to do.  I would like to make duplicate records in my new query based on the number in the QTY field.




Would like to get this:


Thanks

[orange]

Can you tell us why you want to do this? There may be options/alternatives --once we understand the requirement and rationale.

[Mazkot]

I'm looking to import the data into UPS Worldship and it cannot create the package details as they are kept in my database...they need a seperate line item for each package.

Can you tell us why you want to do this? There may be options/alternatives --once we understand the requirement and rationale.

[accesstos]

You need a new table (say tblDuplicates) to keep only the PNUMs.

Copy the code below in a standard code module:

Sub DuplicateRows()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Long
    
    Set db = CurrentDb
    db.Execute "DELETE * From tblDuplicates", dbFailOnError
    Set rs = db.OpenRecordset("SELECT PNUM, QTY FROM tblOriginal", dbOpenForwardOnly)
    With rs
        If Not (.BOF And .EOF) Then
            While Not .EOF
                For i = 1 To Nz(!QTY, 0)
                    db.Execute "INSERT INTO tblDuplicates (PNUM) VALUES ('" & !PNUM & "')", dbFailOnError
                Next i
                .MoveNext
            Wend
        End If
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End Sub

Create a query with that SQL statement:

SELECT tblOriginal.*
FROM tblDuplicates INNER JOIN tblOriginal 
ON tblDuplicates.PNUM = tblOriginal.PNUM;

Run the procedure and open the query to check the results.

Wait for a better solution.

Cheers,
John

[orange]

I agree with John re an approach that seems to work, has a specific purpose and is repeatable. However, I think it would help if you could tell us a little more about the requirement/spec for UPS Worldship and the set up you require.

[Mazkot]

My data right now is consolidated by product number(PNUM). each line has a different PNUM and each PNUM could have more than one qty. UPS's software cannot handle it that way. Each package should have it own record so that it pulls the correct amount of packages into it program for shipping. If I pull the data in like I have it in my database in the above sample, I only get 3 cartons being shipped when I actually should have 6.

[June7]

Can be done in query without VBA. Requires a unique identifier field - autonumber should serve. However, be aware this can perform slowly with very large dataset and then VBA solution might be preferable.

SELECT Data.*
FROM Data, (SELECT DISTINCT [Tens]+[Ones] AS Factor, 10*Abs([Deca].[id] Mod 10) AS Tens, Abs([Uno].[id] Mod 10) AS Ones FROM MSysObjects AS Uno, MSysObjects AS Deca) AS F
WHERE F.Factor<QTY
ORDER BY ID;

Although, this will not alter the BL value as shown in sample output. Seems to be a typo error in the BL values.

[Mazkot]

Thanks. I'll let you know if I run into any issues.