I had a lot of trouble with the syntax in the second SQL statement but I
think its right. I think there must be a better way . Any suggestions are
greatly appreciated!

Public Sub AssignSingleLevels()

    Dim curDiscDNYtd As Currency

    Dim strSinglePurchaseLevel As String
    Dim curYtdSingleLevelTwoDollars As Currency
    Dim curYtdSingleLevelOneDollars As Currency
    Dim curYtdSingleLevelThreeDollars As Currency

        Dim dbs As Database
            Dim rs As Recordset       ' FIRST RECORD SET , LOOP THRU TO
ASSIGN PRICING LEVELS
                Dim dbsSecond As Database
                    Dim rsSecond As Recordset  ' SECOND RECORD SET, THE IDEA
IS TO OPEN A TABLE
                                                                    'AND ADD
THE FIRST PRICING LEVEL TO THE FIRST RECORD

            ' strings
            Dim strDealerGroup As String
            Dim strDealerCode As String
            Dim strProgram As String
            Dim strSQL As String

            ' singles
            Dim sngYtdFactor As Single

            sngYtdFactor = YearToDateFactor

             strSQL = "SELECT tblCommitAssignSingleLevel.DealerGroup,
tblCommitAssignSingleLevel.DealerCode, tblCommitAssignSingleLevel.Program,
Sum(tblCommitAssignSingleLevel.DiscDNYtd) AS SumOfDiscDNYtd,
tblCommitAssignSingleLevel.Level1S, tblCommitAssignSingleLevel.Level2S,
tblCommitAssignSingleLevel.LEVEL3S " _
                   & " FROM tblCommitAssignSingleLevel GROUP BY
tblCommitAssignSingleLevel.DealerGroup,
tblCommitAssignSingleLevel.DealerCode, tblCommitAssignSingleLevel.Program,
tblCommitAssignSingleLevel.Level1S, tblCommitAssignSingleLevel.Level2S,
tblCommitAssignSingleLevel.LEVEL3S "

            Set dbs = OpenDatabase("C:\Commit\CommitDevelopmentJune30.mdb")

            Set rs = dbs.OpenRecordset(strSQL)

            With rs

            If .RecordCount = 0 Then

            MsgBox "Empty Table"
            End If

            rs.MoveFirst

            Do While Not .EOF

            ' fill variables, factor for year to date.

            curDiscDNYtd = rs!SumOfDiscDNYtd

            curYtdSingleLevelOneDollars = (rs!Level1S) * sngYtdFactor
            curYtdSingleLevelTwoDollars = (rs!Level2S) * sngYtdFactor
            curYtdSingleLevelThreeDollars = (rs!Level3S) * sngYtdFactor

             ' Calculate year to date Single levels based on ytd purchases

    If curDiscDNYtd >= curYtdSingleLevelThreeDollars Then

       strSinglePurchaseLevel = 3

        ElseIf curDiscDNYtd >= curYtdSingleLevelTwoDollars Then

            strSinglePurchaseLevel = 2

              ElseIf curDiscDNYtd >= curYtdSingleLevelOneDollars Then

               strSinglePurchaseLevel = 1

                  ElseIf curDiscDNYtd < curYtdSingleLevelOneDollars Then

                       strSinglePurchaseLevel = 0

     End If

             strSQL = "UPDATE tblRenameThis SET NewSingleLevel = '" &
strSinglePurchaseLevel & "'"

             Set dbsSecond =
OpenDatabase("C:\Commit\CommitDevelopmentJune30.mdb")

            Set rsSecond = dbs.OpenRecordset(strSQL)

            .MoveNext

            Loop

    End With

        rs.Close
        dbs.Close
        Set rs = Nothing
        Set dbs = Nothing

            rsSecond.Close
            dbsSecond.Close
                Set rsSecond = Nothing
                Set dbsSecond = Nothing

End Sub

strSQL = "UPDATE tblRenameThis SET NewSingleLevel = '" &
strSinglePurchaseLevel & "'"

             Set dbsSecond =
OpenDatabase("C:\Commit\CommitDevelopmentJune30.mdb")

            Set rsSecond = dbs.OpenRecordset(strSQL)

Any suggetions or comments are greatly appreciated.

"Action queries" don't create recordsets: only a Select does that. Use the
RunSQL method:

   DoCmd.RunSql strSQL

--

Doug Steele, Microsoft Access MVP
Beer, Wine and Database Programming. What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://I.Am/DougSteele/