Average Last 5 Records?

[DJKAOS]

How can I average the last five records of a Querry?
I am able to average all the records, but I just need the last 5 most recent ones.

I have tried to use Recordset.MoveLast, then Recordset.MovePrevious 5 times.....and keep track of totals. But its seems way to complicated is there not a function built in that I can use in my Form?

Thanks for any help.

 

[link9]

Well, if you are just wanting the last five records, and they are date-stamped (or it has some other field you can sort by to get the most recent records in the top of a query), then you could make a query that would get all the records using an ORDER BY fieldName DESC, then run a query on those results and ask for the TOP 5 --

I tried to use a BOTTOM 5, but Access didn't seem to like that very much --

It's the only way I could think of, and I'm not really sure that it would be much more efficient than the method you have described above....

 

[DJKAOS]

I think that can work, I created a new querry that sorts decending by date and then in the properties of the querry I chose to display only the top 5.

Now my question is, is there a way to include more than one querry as the recordsource in a form?

I tried doing this in the "Record Source" property of my form..I set it like this
Querry1;Querry2

but that doesn't work, it only seems to like 1 Querry in there..

 

[DJKAOS]

Thanks for the help. I created a second querry that sorts desending by date and then grabs only the top 10...however when I put that into my form it is in desending order..as expected but I want it to go in Ascending order in my form and I can't get it to do it no matter what I try.

Thanks for any more tips

 

[link9]

Well, if it's in a recordset, then you can just call the movelast method, and then moveprevious, moveprevious, etc...

such as:
recordset.movelast
while not recordset.bof
'output your record on your form
recordset.moveprevious
wend

 

[ghubbell]

Average the last 5 records in a query...

Here's one that you can run in the Northwinds sample Db...go there, open a new query in SQL design and paste this:

SELECT DISTINCTROW TOP 5 [Order Details].OrderID, Avg([Order Details].UnitPrice) AS [Avg Of UnitPrice]
FROM [Order Details]
GROUP BY [Order Details].OrderID
ORDER BY [Order Details].OrderID DESC;

As an example I'm looking for the average unit price on the last 5 orders (by their order number). Flip to QBE view and you'll see it clearly. Gord
ghubbell@total.net

 

[mjksoft]

Dear
How are you, I have a very small nice query
how to SQL statement for select TOP n salary from scott.emp table which created in oracle 8 database and I used ODBC for connectivity ??

thanks alot
M Atta