Loop through Query

[angerplea]

Any idea's for this one? I have a part table summary with various quantities 1-1000 and want to create a new table where part number
repeats with quantity of 1 corresponding back to the sum. If part 123456=20 then this would repeat 20 x and each record Qty=1

Start with summary
--================
Part Qty
111000 2
222000 3

End result all Qty=1
--================
Part Qty
111000 1
111000 1
222000 1
222000 1
222000 1
--================

I stared with a loop and was able to get an append query to work referring to the quantity value (3) for one record from tbl_temp to tbl_main, but not really sure how to advance through many records.

For n = 1 To [Forms]![MainScreen]![Text7]
DoCmd.OpenQuery "qry_Update_Qty"
'DoCmd.GoToRecord , , acNewRec
Next n

maybe a do while or some other approach?

cheers

 

[jdraw]

Seems you are trying to recreate some data records. Why?

 

[angerplea]

it uploads into a program that way. I just need to get the parts to reapeat based on the values contained in the quantity field.

 

[jdraw]

Here is a mock up to show the logic.

There is a subroutine MakeRecord

Sub makeRecords(RecVal As String, MakeQTY As Integer)

    Dim SQL As String, i As Integer
   On Error GoTo makerecords_Error

    SQL = "Insert into YourTable Values ('" & RecVal & "')"
    
    If Len(RecVal & "") > 0 Then
        For i = 1 To MakeQTY
           ' CurrentDb.Execute SQL, dbFailOnError
           Debug.Print SQL
        Next i
    End If

   On Error GoTo 0
   Exit Sub

makerecords_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure makerecords of Module Module1"
End Sub

And a test routine

Sub test1()
Dim a As String
Dim b As Integer
   On Error GoTo test1_Error

a = "pwerty_ThisIsANewRecord"  'field value
b = 5                          'number of records to create
Call makeRecords(a, b)

   On Error GoTo 0
   Exit Sub

test1_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure test1 of Module Module1"
End Sub

The test routine does not use your query. And it does not execute the append query.
It does show you how to pass parameters to the subroutine, and how to execute the query the appropriate number of times to build the records.

result:
1 Insert into YourTable Values ('pwerty_ThisIsANewRecord')
2 Insert into YourTable Values ('pwerty_ThisIsANewRecord')
3 Insert into YourTable Values ('pwerty_ThisIsANewRecord')
4 Insert into YourTable Values ('pwerty_ThisIsANewRecord')
5 Insert into YourTable Values ('pwerty_ThisIsANewRecord')


Good luck