Loop a query until there are no records

[scubadiver007]

I have an excel link table and 24 of the columns are named "Attendee outcomes1" to "Attendee outcomes24".

The following query is a much shorter version (but this is essentially it):

INSERT INTO tble_record_comp ( ListNo, FamilyID, MemberID, Attendee_outcome )
SELECT Import_Link.List, Import_Link.[Family ID], Import_Link.[Member ID], Import_Link.[Attendee Outcomes1]
FROM Import_Link
WHERE (((Import_Link.[Attendee Outcomes1]) Is Not Null));

Every time I loop the query I want the "attendee outcomes" column to change (from 1 to 2 to 3 etc).

Also, at some point there will be an empty column and if the query returns no records I want the loop to stop.

Thanks

 

[Ranman256]

For I = 1 to 24
    SSql =" insert into ...[attendee_outcome" & I & "] is not null"
    Docmd.runSql sSql
Next

 

[Minty]

You'll have to create the query dynamically to change the field reference.

    Dim sInsSQL As String
    Dim sSelSQL As String
    Dim i As Integer
    Dim rsSel As Recordset
    Dim db As Database

    sInsSQL = "INSERT INTO tble_record_comp ( ListNo, FamilyID, MemberID, Attendee_outcome ) "
    Set db = CurrentDb

    For i = 1 To 24
    
        sSelSQL = sSelSQL & "SELECT Import_Link.List, Import_Link.[Family ID], Import_Link.[Member ID], Import_Link.[Attendee Outcomes" & i & "] "
        sSelSQL = sSelSQL & "FROM Import_Link "
        sSelSQL = sSelSQL & "WHERE Import_Link.[Attendee Outcomes" & i & "] Is Not Null "
    
        Set rsSel = db.OpenRecordset(sSelSQL)
    
        If rsSel.EOF Then
            rsSel.Close
            Set rsSel = Nothing
            Exit Sub
        End If
    
        db.Execute sInsSQL & sSelSQL
        rsSel.Close
        Set rsSel = Nothing
    Next i

Edit - bugger Ranman beat me to it....

 

[scubadiver007]

For I = 1 to 24
    SSql =" insert into ...[attendee_outcome" & I & "] is not null"
    Docmd.runSql sSql
Next

Thanks for this but I want to stop the loop as soon a query returns no records if possible. If it isn't not a problem

 

[static]

Dim db As dao.Database
Set db = CurrentDb
For i = 1 To 24
        sSelSQL = ""
        sSelSQL = sSelSQL & "SELECT Import_Link.List, Import_Link.[Family ID], Import_Link.[Member ID], Import_Link.[Attendee Outcomes" & i & "] "
        sSelSQL = sSelSQL & "FROM Import_Link "
        sSelSQL = sSelSQL & "WHERE Import_Link.[Attendee Outcomes" & i & "] Is Not Null "

    db.Execute sSelSQL
    If Not db.RecordsAffected Then Exit For
Next

 

[arnelgp]

you may also create a function in a Module and call it in your Insert Query.

Public Function fncMaxValue(ParamArray p() As Variant) As Variant
    Dim varRet As Variant
    Dim i As Integer
    For i = 0 To UBound(p)
        If Not IsNull(p(i)) Then
                varRet = p(i)
        End If
    Next
    fncMaxValue = varRet
End Function

then your query will be:

INSERT INTO tble_record_comp ( ListNo, FamilyID, MemberID, Attendee_outcome )
SELECT Import_Link.List, Import_Link.[Family ID], Import_Link.[Member ID], fncMaxValue([Import_Link].[Attendee outcomes1],[Import_Link].[Attendee outcomes2],[Import_Link].[Attendee outcomes3],[Import_Link].[Attendee outcomes4],[Import_Link].[Attendee outcomes5],[Import_Link].[Attendee outcomes6],[Import_Link].[Attendee outcomes7],[Import_Link].[Attendee outcomes8],[Import_Link].[Attendee outcomes9],[Import_Link].[Attendee outcomes10],[Import_Link].[Attendee outcomes11],[Import_Link].[Attendee outcomes12],[Import_Link].[Attendee outcomes13],[Import_Link].[Attendee outcomes14],[Import_Link].[Attendee outcomes15],[Import_Link].[Attendee outcomes16],[Import_Link].[Attendee outcomes17],[Import_Link].[Attendee outcomes18],[Import_Link].[Attendee outcomes19],[Import_Link].[Attendee outcomes20],[Import_Link].[Attendee outcomes21],[Import_Link].[Attendee outcomes22],[Import_Link].[Attendee outcomes23],[Import_Link].[Attendee outcomes24]) AS Expr1
FROM Import_Link
WHERE (((IsNull(fncMaxValue([Import_Link].[Attendee outcomes1],[Import_Link].[Attendee outcomes2],[Import_Link].[Attendee outcomes3],[Import_Link].[Attendee outcomes4],[Import_Link].[Attendee outcomes5],[Import_Link].[Attendee outcomes6],[Import_Link].[Attendee outcomes7],[Import_Link].[Attendee outcomes8],[Import_Link].[Attendee outcomes9],[Import_Link].[Attendee outcomes10],[Import_Link].[Attendee outcomes11],[Import_Link].[Attendee outcomes12],[Import_Link].[Attendee outcomes13],[Import_Link].[Attendee outcomes14],[Import_Link].[Attendee outcomes15],[Import_Link].[Attendee outcomes16],[Import_Link].[Attendee outcomes17],[Import_Link].[Attendee outcomes18],[Import_Link].[Attendee outcomes19],[Import_Link].[Attendee outcomes20],[Import_Link].[Attendee outcomes21],[Import_Link].[Attendee outcomes22],[Import_Link].[Attendee outcomes23],[Import_Link].[Attendee outcomes24])))=False));

 

[scubadiver007]

Thanks both but I don't need to make it complicated.