Query for average of the last 10 records

I have a table of Waste IDs, with a relationship with a second table of Waste Weights:
WasteIDs
WasteWeights:
I want to look up the last 10 wasteweights for an id, and get an average.
So I can do:
to get the last 10, but this only works when i put in particular IDs, I want the average of the top 10 for each ID so I can have it as part of a report
so the final datasheet will be like:
Any ideas? Could it be done with multiple queries maybe?

re: Query for average of the last 10 records

I think you'll need multiple queries I'm afraid.

re: Query for average of the last 10 records

No guarantees, but this may work :

re: Query for average of the last 10 records

Hmm, I get a 'syntax error in FROM clause', and it highlights the later one but even if you attempt putting the innermost query in, you get the same error

It doesn't like the placement of 'As' or using keywords Inner and Outer, but even once those are altered (which could be breaking its intended use horribly, sorry):

I get the 'streamid is no included in an aggregate function' error

re: Query for average of the last 10 records

If it doesn't work then it doesn't.

I don't have any live data to test on so just knocked something up. I wouldn't really expect it to work in Access (it may in SQL Server or other such full-featured SQL server).

I'm afraid your proposed solution will not return correct data even if it were to run. It's not asking the right question.

I think you need to return to the idea of multiple queries for this.

re: Query for average of the last 10 records

Thank you for your help! I think I give up now, and I'll just settle on the overall average, rather than the average fo the last X records, its a bit too much for my poor brain to handle!

Thanks,
Charli

re: Query for average of the last 10 records

It's certainly not straightforward Charli.

The TOP Predicate is rarely ever usable outside of the main, outer query in my experience. Unless a specific grouping is selected of course. Where it would require running more than once to make sense (as in this case) it will always be hard if even possible.

re: Query for average of the last 10 records

How I ended up doing this, incase anyone has the same problem:

Query 1 (called top10): SELECT TOP 10 streamid FROM table WHERE streamid=[Reports]![Confirm]![streamid] ORDER by [date] DESC;
-- returns the top 10 most recent records for an id

Query 2 (called Avgtop10): SELECT AVG(top10) FROM top10 WHERE streamid = [Reports]![Confirm]![streamid];
-- returns the average of the top 10 most recent records for an id

Then put Query Avgtop10 into a report (removing all the headers)
Made a second report (called Confirm) with a list of streamids (just SELECT streamid FROM table), embed the Avgtop10 report as a sub report.

This is a very long and convoluted way to get a list of IDs, and the average of the 10 most recent records for each id!

re: Query for average of the last 10 records

I'm not sure it is Charli.

It's convoluted yes, but then the requirement is convoluted. This seems to be a sensible solution to the problem. No more complex than you would reasonably expect for such a problem (in my view at least).

Congratulation for the solution by the way.