Sub DumbButCool()
 
    Dim mdb As Database
    Dim strSQL As String
 
On Error GoTo Done
 
    DBEngine(0).BeginTrans
    Set mdb = DBEngine(0)(0)
    strSQL _
        = " DELETE FROM Table1" _
        & " WHERE SomeText Not Like 'b*'"
    mdb.Execute strSQL
    strSQL _
        = " SELECT SomeText, Sum(SomeNumber) As Total" _
        & " INTO Table2 FROM Table1" _
        & " GROUP BY SomeText"
    mdb.Execute strSQL
    strSQL _
        = " SELECT Count(*) As N, Avg(Total) As A" _
        & " FROM Table2"
    With mdb.OpenRecordset(strSQL, dbOpenSnapshot)
        Debug.Print !N, !a
    End With
    ' DoCmd.OpenTable "Table2"
    
Done:
    If Err Then MsgBox Err.Description: Err.Clear
    DBEngine(0).Rollback
 
End Sub

Select all