average for few records

[Guest]

hello,

i have list of sequant records.

i have to calculate the average but just of the last three records.

how ? with 'davg' function ?

if yes, what to right in the criterya field ?

thanks

 

[Guest]

tblValues.Fields:
ID - dbLong, autonumber
dblValue - dbDouble

qry
SELECT Avg(tblValues.dblValue) AS avgValue
FROM tblValues
WHERE (((tblValues.ID) In (SELECT TOP 3 tblValues.ID FROM tblValues
ORDER BY tblValues.ID DESC)));

HTH

Vlado

 

[Guest]

thank you,

it does not recognize the "top 3"

where exactly to write it ?

 

[Guest]

Create a query and pass the SQL-string. If you'll run the query you'll see
the result. Then you have many options to use the result.

1) Create a new query and open it in design mode.
2) Switch to SQL (there's a button on the command bar).
3) Copy and paste the SQL that I posted... & adapt the SQL-string to your
database.
4) Test the query: click the Run (!) button in command bar.
5) Save the query.

One of the options how to display the result:
1) Create an unboud text-box in form or report.
2) Set ControlSource to =DLookup("avgValue"; "queryNameAsYouNamedIt")

Pls, read help.

HTH

Vlado

 

[Guest]

OK, thank you.

but now the problem is that i have for each id many sequent records (dates)
that not equal from one id to other id.
i want the "top 3" to give me the top 3 for each id.
how ?

thanks again

 

[Guest]

I don't understand what you mean. Autonumber data type can't result
duplicities.
Pls, be sure to read help.

Vlado

OK, thank you.

but now the problem is that i have for each id many sequent records
(dates)
that not equal from one id to other id.
i want the "top 3" to give me the top 3 for each id.
how ?

thanks again