Calculation using the record above in the same feild

  • Thread starter Thread starter Mark Williams via AccessMonster.com
  • Start date Start date
M

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
 
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?
 
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
 
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])
 
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

Steve said:
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
Click to expand...
Click to expand...
 
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.

Steve said:
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
Click to expand...
Click to expand...
 
Back
Top