Software
I have a field called "Price".
I would like to read all records in the table and average the Price field by 7 days, 14 days, 21 days, etc.
e.g. I'd like to have a new Query field that averages the last 7 records, and another field that averages the last 14 records, etc.
by bob sellman
The simplest way is actually to write a report that uses the source table sorted by date descending.
You could just have a detail area and a report footer and do the following to calculate and display the averages for 7 days, 14 days, 21 days, 28days (and however many you want).
Make the detail area not visible or at least zero height.
Include in the detail area one control (txtPrice) that contains the value of the Price field and a second control (txtDate) that contains the date. Inthe report footer have controls for txtAvg7, txtAvg14, txt Avg21, (etc.), plus hidden controls for txt7days, txt7totprice, txt14days, txt14totprice, etc. txtAvg7 = txt7totprice/txt7days, txtAvg14=txt14totprice/txt14days, etc.
In the detail report, probably in the on format property (rather than on print property), put code that basically looks at the date, compares to the starting date, and then adds to the appropriate fields in the report footer.
Something like this (I haven't checked this code, just writing it off the top of my head):
Dim dteStart as date ' starting date
dim intDays as integer
if IsNull(dteStart) then
dteStart = me.txtDate
else
intDays = DateDiff("d",me.txtDate,dteStart)
end if
if intdays <8 then
me.txt7Days = nz(me.txt7days) + 1
me.txt7totprice = nz(me.txt7totprice) + me.txtPrice
end if
if intdays <15 then
me.txt14Days = nz(me.txt14days) + 1
me.txt14totprice = nz(me.txt14totprice) + me.txtPrice
end if
*continue for all of the periods ofinterest
Note that the values are added to all items except those where the number of days is less than the desired number.
by johngreer
by johngreer
This discussion is closed to new replies.
Sort by Trending