Subquery vs GetRows method 1

[bajo71]

Hello,

I am attempting to populate a field named Beginning Amt with the value in a field named Ending Amt from the previous month. The schema and data are as follows...

EFF_DATE SEGMENT ENDING_AMT BEGINNING_AMT
2008-01-01 104777 2350
2008-02-01 104777 6599 2350

Right now I have the following VBA code using the GetRows method looping through 20,000 records twice. This is taking an incredibly long time. Could this be accomplished using a subquery?

Thanks...........ricky

Function PrevMonthAMT()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim RowCount As Long, i As Long, j As Long
Dim SEGMENT As Variant, EFF_DATE As Variant, varData As Variant

Set db = CurrentDb()
Set rs = db.OpenRecordset("AL_EVNT_MANUAL_FINANCIAL", dbOpenSnapshot)

RowCount = rs.RecordCount
varData = rs.GetRows(RowCount)

For i = 0 To RowCount - 1 'GetRows method is zero based
EFF_DATE = varData(0, i)
SEGMENT = varData(2, i)

For j = 0 To RowCount - 1
If varData(0, j) = DateAdd("m", -1, EFF_DATE) And varData(2, j) = SEGMENT Then
varData(4, i) = varData(3, j)
rs.Update
End If
Next j
Next i
rs.Close

 

[Ed2020]

I'd use a function to return the Starting Amount for a given record. Assuming that Effective Date is your primary key the following should do what you want (you'll need to consider exception handling):

Public Function GetStartingAmount(ByVal dtmEffectiveDate As Date) As Long
    Dim ThisDB As DAO.Database
    Dim rstAmount As DAO.Recordset
    
    Set ThisDB = CurrentDb()
    Set rstAmount = ThisDB.OpenRecordset("SELECT * FROM tblExample WHERE EFF_DATE=#" & Format(DateAdd("m", -1, dtmEffectiveDate), "mm/dd/yyyy") & "#;", dbOpenSnapshot)
    
    GetStartingAmount = rstAmount!ENDING_AMT
End Function

You can then update your table with an update query, calling the function you have created. Alternatively the value doesn't necessarily need to be stored in the table - you may be able to add a call to this function behind what ever forms, reports you want to display it on.

The update query ran for me in a few minutes.

Ed Metcalfe.

Please do not feed the trolls.....

 

[PWise]

you an run this in a simple update query

UPDATE AL_EVNT_MANUAL_FINANCIAL as tm INNER JOIN AL_EVNT_MANUAL_FINANCIAL AS lm ON (tm.SEGMENT= lm.SEGMENT) AND (tm.StartDate = datedaa("m",-1,lm.nextmonth)) SET tm.[BEGINNING_AMT] = lm.ENDING_AMT