SQL Explode Lines

If you have a select sql statement that returns the following rows

INVCode  DESC   Qty    Price
123      K6-300    2    50.00
124      CD40      3    20.00

How can I transform this with a SQL statement to:

INVCode  DESC      Price
123      K6-300    50.00
123      K6-300    50.00
124      CD40      20.00
124      CD40      20.00
124      CD40      20.00

IOW I need to make multiple rows depending on the count.
If this is harder than it seems I'll up the points.
davidpmAsked:
Who is Participating?
 
RRRConnect With a Mentor Commented:
Sorry , some corrections:
Public Function MultiplyFields()
Dim db As Database, rsSource As Recordset, rsDestin As Recordset
Dim tbSource As TableDef, tdDestin As TableDef
Dim i As Long

    Set db = CurrentDb
    Set tbSource = db.TableDefs("MyTable")
    Set rsSource = tbSource.OpenRecordset
   
    Set tdDestin = db.TableDefs("AuxTable")
    Set rsDestin = tdDestin.OpenRecordset
    rsSource.MoveFirst
    While Not rsSource.EOF
        rsDestin.AddNew
        For i = 1 To rsSource!Qty
            rsDestin!INVCode = rsSource!INVCode
            rsDestin!DESC = rsSource!DESC
            rsDestin!Price = rsSource!Price
        Next
        rsDestin.Update
        rsSource.MoveNext
    Wend
   
    Set rsSource = Nothing
    Set rsDestin = Nothing
    Set tbSource = Nothing
    Set tdDestin = Nothing
    Set db = Nothing
   

End Function

RRR.
0
 
RRRCommented:
Hi, davidpm.
You should use this solution:
1)Create some auxilary table with 3 fields: INVCode, DESC, Price.
2)By using recordset that opened to Qty field fill this aux table.
If you want I can paste the code.

Good Luck
RRR.
0
 
RRRCommented:
Here some funtion:

Public Function MultiplyFields()
Dim db As Database, rsSource As Recordset, rsDestin As Recordset
Dim tbSource As TableDef, tdDestin As TableDef
Dim i As Long

    Set db = CurrentDb
    Set tbSource = db.TableDefs("MyTable")
    Set rsSource = tbSource.OpenRecordset
   
    Set tdDestin = db.TableDefs("AuxTable")
    Set rsDestin = tdDestin.OpenRecordset
    While Not rsDestin.EOF
        For i = 1 To rsSource!Qty
            rsDestin!INVCode = rsSource!INVCode
            rsDestin!DESC = rsSource!DESC
            rsDestin!Price = rsSource!Price
        Next
    Wend
   
    Set rsSource = Nothing
    Set rsDestin = Nothing
    Set tbSource = Nothing
    Set tdDestin = Nothing
    Set db = Nothing
   

End Function


Good Luck
RRR.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
davidpmAuthor Commented:
Yes walking the fields with code is an option. I suspect that the addnew and update have to be inside the for loop though?
I wonder if this can be done with SQL?
0
 
BrianWrenCommented:
I don't think that that can be done with SQL...

And you are right about the AddNew/UpDate.

    With rsSource
    While Not .EOF
        For i = 1 To !Qty
            rsDestin.AddNew
            rsDestin!INVCode = !INVCode
            rsDestin!DESC = !DESC
            rsDestin!Price = !Price
            rsDestin.Update
            .MoveNext
        Next
    Wend
    End With

Brian
0
 
davidpmAuthor Commented:
O my. So close. I believe the move next has to be out of the for loop.

I was playing with an insert sql clause followed by an update that reduces qty by one. Still looping but more sql.

0
 
RRRCommented:
OK. Anything else?? :))
After first pasted function I pasted new one updated with addnew and update.

davidpm: Did you tried it???

RRR.
0
 
davidpmAuthor Commented:
It appears as if what I really wanted to do is not possible so I am giving up the points to rrr as I used some of his code.
Thank you all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.