Reverse Pivot a Result Set into One Column 1

[Fattire]

I would like to take:
INVOICE|QTY
12345 |500,400,700,600

And turn it into:
INVOICE|QTY
12345 |500
12345 |400
12345 |700
12345 |600

Any sites or previous links you know of? I couldn't find anything.

 

[dhookom]

Fattire,
Are there always four and only four quantities?
Are they always delimited with a comma?
Is there only one invoice record?


Duane
Hook'D on Access
MS Access MVP

 

[Fattire]

There can as many as 48 quantities and they are always delimited with a comma. The invoice will never appear in more then one row.

 

[Fattire]

But there can be thousands of invoices in a single import...

 

[dhookom]

Try code like:

Public Function NormalizeFattire()
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim strSQL As String
    Dim intQty As Integer
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT INVOICE,QTY FROM tblFattire1")
    With rs
      .MoveFirst
      Do Until .EOF
        For intQty = 0 To UBound(Split(!Qty, ","))
          strSQL = "INSERT INTO tblFattire2 (INVOICE, Qty) VALUES (" & _
              !INVOICE & "," & Split(!Qty, ",")(intQty) & ")"
          db.Execute strSQL, dbFailOnError
        Next
        .MoveNext
      Loop
      .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End Function

Duane
Hook'D on Access
MS Access MVP

 

[Fattire]

you are a magnificent god...