General discussion

Locked

Access 2000, using data from all records

By JohnGreer ·
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.

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access 2000, using data from all records

by Bob Sellman In reply to Access 2000, using data f ...

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.

Collapse -

Access 2000, using data from all records

by JohnGreer In reply to Access 2000, using data f ...

Sorry, either I did it wrong or I don't understand or...

Collapse -

Access 2000, using data from all records

by JohnGreer In reply to Access 2000, using data f ...

This question was closed by the author

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums