Calculation using the record above in the same feild

[Mark Williams via AccessMonster.com]

I'm trying to replicate an excel calculation in an Access query.

I've got a feild called "AverageGain"

Firstly it runs a 14 day average over the feild "Gain"

okay, but then -

record 15 of "AverageGain" is calculated by multipling record 14 of
"AverageGain" by 13 (and adding record 15 of "Gain")

and record 16 of "AverageGain" is calculated by multipling record 15 of
"AverageGain" by 13 (and adding record 16 of "Gain")

in excel you'd simply use a reference to the above cell.
in access the same style expression seems to create a circular reference


can this be done in the design view of a query? a function?

I'm not too sharp on code, (and that's not all) but if there's some 'n-1'
type code that works here a posting with explanation for dummies would be
great

many thanks

mw

 

[Steve Schapel]

Mark,

In Access, you can't use the concept of "record 15", "record 16", etc.
Which record comes above or below or before or after is arbitrary unless
you use actual data to designate the order. One way this is often done
is to have a WhenCreated field in the table that records the date/time
the record was first entered, so you can use this to determine order of
records. Do you have any such thing?

 

[Mark Williams via AccessMonster.com]

Hi Steve

I don't have a WhenCreated feild, but I do have a numeric "ID" feild which
designates the order of the records.

Can I / how do I use this as a reference to tie it into the "AverageGain"
feild and do the calc?

At the moment my calc in the design view of the query is;

AverageGain: ((([AverageGain]-1)*13)+[Gain])/14

where ([AverageGain]-1) would like to be the record of AverageGain (eg "ID"
15) above the record of AverageGain being calculated(eg "ID" 16)

many thanks

 

[Steve Schapel]

Mark,

I got a bit confused with the 13s and 14s in your example. But, you
mean like this?...
AverageGain: DAvg("[Gain]","YourTable","[ID]<=" & [ID])

 

[John Spencer (MVP)]

Pardon me for jumping in. If you want a 14-day running average then I think the
solution might be to use a coordinated sub-query.

SELECT A.*,
(SELECT TOP 14 Avg(B.[Gain])
FROM YOURTable As B
WHERE B.ID <= A.ID
ORDER BY ID DESC) As AvgGain
FROM YourTable as A

Mark,

I got a bit confused with the 13s and 14s in your example. But, you
mean like this?...
AverageGain: DAvg("[Gain]","YourTable","[ID]<=" & [ID])

--
Steve Schapel, Microsoft Access MVP

Hi Steve

I don't have a WhenCreated feild, but I do have a numeric "ID" feild which
designates the order of the records.

Can I / how do I use this as a reference to tie it into the "AverageGain"
feild and do the calc?

At the moment my calc in the design view of the query is;

AverageGain: ((([AverageGain]-1)*13)+[Gain])/14

where ([AverageGain]-1) would like to be the record of AverageGain (eg "ID"
15) above the record of AverageGain being calculated(eg "ID" 16)

many thanks

 

[Steve Schapel]

Thanks a lot, John. If that's what Mark wants, I would never have
guessed it!

 

[John Spencer (MVP)]

No problem. I just read the Mark's request differently than you. I'm not sure
which of us is correct (if either). I kind of ignored his method and went with
my understanding of a running sum.

Thanks a lot, John. If that's what Mark wants, I would never have
guessed it!

--
Steve Schapel, Microsoft Access MVP

Pardon me for jumping in. If you want a 14-day running average then I think the
solution might be to use a coordinated sub-query.

SELECT A.*,
(SELECT TOP 14 Avg(B.[Gain])
FROM YOURTable As B
WHERE B.ID <= A.ID
ORDER BY ID DESC) As AvgGain
FROM YourTable as A