Multiple entries into single entries

[Neokey]

Can anyone help please.

I have a table with part numbers, cost and qty, that i would like to split into a single row for each item.

I have attached an example.

Any help would be great.

Neo

 

[Ranman256]

That is a terrible way to do it and not how queries work.

 

[plog]

Why? And, this isn't going to be a query, its going to take VBA. What you would need to do is pull that (and any other records you want to expand) into a recordset (google that) then loop through the recordset and then loop the appropriate number of times for the QTY field and each of those loop you create an INSERT statement to add a record to a new table. Your new table would then hold all the data you need.

Again, though, why?

 

[arnelgp]

save it in another table, say table2 in my example:

Public Sub SplitTrans()
    Dim db As dao.Database
    Dim rsSource As dao.Recordset
    Dim rsTarget As dao.Recordset
    Dim i As Integer, iEnd As Integer
    Set db = CurrentDb
    Set rsSource = db.OpenRecordset("tableNametToSplit", dbOpenDynaset)
    Set rsTarget = db.OpenRecordset("table2", dbOpenDynaset)
    With rsSource
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            iEnd = !Qty
            If iEnd > 1 Then
                For i = 1 To iEnd
                    With rsTarget
                        .AddNew
                        ![Location] = rsSource![Location]
                        ![Part No] = rsSource![Part No]
                        ![Price] = rsSource![Price]
                        ![Qty] = 1
                        ![Total] = rsSource![Total] / rsSource![Qty]
                        .Update
                    End With
                Next i
            End If
            .MoveNext
        Wend
    End With
    rsSource.Close
    rsTarget.Close
    Set rsSource = Nothing
    Set rsTarget = Nothing
    Set db = Nothing
End Sub

 

[Neokey]

Thank you I'll give this a try.

 

[Neokey]

I Must say thank you again as this worked a treat