Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Compare Balances in large dataset.

Status
Not open for further replies.

citychap26

Programmer
Sep 19, 2004
144
GB
Hi,

Been out of practice of coding for a while, please can you tell me if there is a better way of doing this.

Should I be using a recordset clone ?

Cheers

SK

Code:
Function flag_wrong_move()

    On Error GoTo ERR_HANDLER
    
    Dim db As Database
    Dim rst As DAO.Recordset
    Dim sSQL As String
    Dim iCURR_AC_RK As Long
    Dim iNEXT_AC_RK As Long
    Dim iCURR_MONTH As Integer
    Dim iNEXT_MONTH As Integer
    Dim iCURR_BAL As Long
    Dim iNEXT_BAL As Long
    Dim iDIFF_BAL As Long
    Dim iCOUNT As Double
    
    sSQL = "select * from tbl_004_MORTGAGE_BALANCES ORDER BY ACCOUNT_RK, V_FROM_YEAR, V_FROM_MONTH"
    iCOUNT = 0
        
    Set db = CurrentDb()
    Set rst = db.OpenRecordset(sSQL, dbOpenDynaset)
    
    With rst
        .MoveFirst
        Do Until .EOF
            
            iCURR_AC_RK = .Fields(0)
            iCURR_MONTH = .Fields(1)
            iCURR_BAL = .Fields(4)
            '--- Get details of next record.
            '--- If Account_RK is different then move on, else compare.
            If Not .EOF Then

                .MoveNext
                iNEXT_AC_RK = .Fields(0)
                iNEXT_MONTH = .Fields(1)
                iNEXT_BAL = .Fields(4)
                .MovePrevious
                
                If iCURR_AC_RK = iNEXT_AC_RK Then
                    'Debug.Print iCURR_AC_RK & ", " & iNEXT_AC_RK
                    
                    '--- This part of the lovely code will catch instances where there are missed months.
                    '--- Should write to a table really.
                    'If iCURR_MONTH + 1 <> iNEXT_MONTH And iCURR_MONTH <> 12 Then
                        'Debug.Print "**** PROBLEM MISSED MONTH"
                        'Debug.Print "CURRENT_MONTH: " & iCURR_MONTH
                        'Debug.Print "NEXT_MONTH: " & iNEXT_MONTH
                    'End If
                    
                    '--- Update the difference column
                    iDIFF_BAL = iNEXT_BAL - iCURR_BAL
                    With rst
                        '--- we are comparing to previous month so need to move forward one.
                        .MoveNext
                        .Edit
                        .Fields("MONTH_CHANGE").Value = iDIFF_BAL
                        .Update
                        .MovePrevious
                    End With
                End If
            
            End If
        .MoveNext
        iDIFF_BAL = 0
        iCOUNT = iCOUNT + 1
                
        If iCOUNT Mod 1000 = 0 Then
            DoEvents
            Debug.Print iCOUNT & " " & Now()
        End If
                
        Loop
    End With
    
NORMAL_EXIT:

    Exit Function
    

ERR_HANDLER:

    Select Case Err.Number
        Case 3052
            Resume Next
        Case Else
            MsgBox Err.Number & ", " & Err.Description
            Debug.Print "WORKING ON : " & iCURR_AC_RK & ", " & iCURR_MONTH
            Resume Next
        End Select

End Function
 
I think I would do it all in sql.

I would probably do it in two queries.
qryGoodMonths: Return records where A.Month = B.Month - 1

qryBadMonths: Return records where there is not a B.month - 1

Then the rest are just simple calculated fields.

Then the results can easily go into a make table query.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top