Query Calculation using previous result

[Erick]

I have searched this forum and also googled this and found a few results but nothing that really made it very clear to me.

I have a table with an index field. I need a query that performs a calc and uses the previous record (Index - 1) in the calculation.

I know how to lookup the previous record (or more accurately the record with index-1) from the original table.

The problem that I have is I need to look up the previous calculated value and this can only come from the query that I am writing and not a pre-existing table.

tblData
Index ----Data
1 -------0.95
2 -------0.90
3 -------0.85
4 -------0.91
5 -------0.88
6 -------0.93
7 -------0.92
8 -------0.95
9 -------0.88
10 ----- 0.96

So if I am creating Query1, I have these 2 fields:
Index: [tblData].[Index]
qryData:IIF([tblData].[Index]=1,[tblData].[Data], {somefunction} * DLOOKUP("qryData", "Query1", "[tblData].[Index] = " & [tblData].[Index] -1)

The query is not working and I suspect it is because I am referencing itself in my DLookup formula. How can this be done?

just for interest I am calculating an exponential moving average here, which I just summarise as {somefunction} in the above example for simplicity.

 

[SydneyGeek]

Erick, create a second query to do the referencing. Use query1 as the data source.
Often, a complex calc like that is best separated into 2 chunks.

Denis

 

[Erick]

Erick, create a second query to do the referencing. Use query1 as the data source.
Often, a complex calc like that is best separated into 2 chunks.

Denis

Hello again Denis

I thought about that, but can't quite work out how to set it up.

What do I query in the 1st query that I can look up in the 2nd? i.e. how do I break it up into 2 chunks of work?

 

[SydneyGeek]

If the index always increments by 1 you can use a subquery to get the previous value. Something like this --

PrevValue:(SELECT T.[Data] FROM tblData AS T WHERE T.Index=tblData.Index-1)

Then use a second query to analyse the 2 data values for each record.

Denis

 

[SydneyGeek]

Another option that doesn't require index values to be consecutive --

PrevData: (SELECT Top 1 T.Data FROM tblData AS T WHERE T.Index<tblData.Index ORDER BY T.Index DESC)<tbldata.index order="" by="" t.index="" desc)<tbldata.index="" desc)

Denis</tbldata.index>

 

[Erick]

Sorry Denis, I'm still not getting something here.

The problem that I have is that I don't need to look up the previous record in the tblData table, I need to look up the previous calculated result from the query itself, like a compounding formula.

 

[james_lankford]

can you not take qryData and make it a "make table" query ?

then make a new query based on the new table and join to tblData

then you can take the data from the new table and use it to make your calculation

 

[SydneyGeek]

Erick, try something like this:

Create a new field to hold the calculated result
In code, loop through the records, writing the calculated value to the new field
At the next record you can then reference the previous value, insert it into the calc, and keep going.

Along these lines (insert your real calc where I did the dummy:

Function WriteCalc()
    Dim dblPrevValue As Double
    Dim dblCurrentValue As Double
    Dim rst As DAO.Recordset
    
    Set rst = CurrentDb.TableDefs("tblData").OpenRecordset
    
    With rst
        .MoveFirst
        Do Until .EOF
            .Edit
            If !Index = 1 Then
                dblCurrentValue = !Data
            Else
                dblCurrentValue = dblPrevValue * 1.102245
            End If
            !calcData = dblCurrentValue
            dblPrevValue = !calcData
            .Update
           .MoveNext
        Loop
    End With
    
    rst.Close
    Set rst = Nothing
End Function

Denis
Denis

 

[Erick]

Thanks Denis. I was hoping it wouldn't get to using functions. I have been avoiding this so far...only because I don't know how to use it.

However, the example you gave me set me on the right path.

Thanks again.