By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,603 Members | 1,478 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,603 IT Pros & Developers. It's quick & easy.

Query for average of the last 10 records

P: 23
I have a table of Waste IDs, with a relationship with a second table of Waste Weights:
WasteIDs
Expand|Select|Wrap|Line Numbers
  1. id  name
  2. 1. Paper
  3. 2. Cardboard
  4. 3. Cans
WasteWeights:
Expand|Select|Wrap|Line Numbers
  1. Id   date   kg
  2. 1. 1/1/09 12
  3. 1. 1/2/09  24
  4. 2. 1/1/09  2
I want to look up the last 10 wasteweights for an id, and get an average.
So I can do:
Expand|Select|Wrap|Line Numbers
  1. SELECT avg(weight)
  2. FROM (SELECT TOP 10 weight
  3.       FROM wasteweights
  4.       WHERE id=1
  5.       ORDER BY [date] DESC)
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:
Expand|Select|Wrap|Line Numbers
  1. Id   Avg
  2. 1. 18
  3. 2.  2
Any ideas? Could it be done with multiple queries maybe?
Jun 23 '09 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,709
I think you'll need multiple queries I'm afraid.
Jun 23 '09 #2

NeoPa
Expert Mod 15k+
P: 31,709
No guarantees, but this may work :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [ID]
  2.      ,   (SELECT Avg([Weight])
  3.           FROM (SELECT TOP 10
  4.                          [Weight]
  5.                 FROM     [WasteWeights] AS Inner
  6.                 WHERE    Inner.ID=Outer.ID
  7.                 ORDER BY Inner.Date DESC)) AS AvgWeight
  8. FROM     [WasteWeights] AS Outer
  9. GROUP BY [ID]
Jun 23 '09 #3

P: 23
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):

Expand|Select|Wrap|Line Numbers
  1. SELECT [streamid], (SELECT Avg([Weight]) as avg  FROM 
  2. (SELECT TOP 10  [Weight] as inn  FROM  [WasteWeights]  WHERE  Inn.streamid=Out.streamID ORDER BY Inn.Date DESC)
  3. AS AvgWeight) AS Out
  4. FROM WasteWeights
  5. GROUP BY [streamID];
  6.  
I get the 'streamid is no included in an aggregate function' error
Jun 23 '09 #4

NeoPa
Expert Mod 15k+
P: 31,709
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.
Jun 23 '09 #5

P: 23
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
Jun 24 '09 #6

NeoPa
Expert Mod 15k+
P: 31,709
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.
Jun 24 '09 #7

P: 23
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!
Jul 9 '09 #8

NeoPa
Expert Mod 15k+
P: 31,709
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.
Jul 30 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.