I'm trying to create a query that gives me the average price of each product 
based on the last 10 purchases. 

The table is called "t_worksheet", with fields called 
"buy_price_alt_currency" which represents the price, "processing_grade" which 
represents the product and "collection_date" which represents the purchase 
date.

I've been struggling with sub queries but can't get the results I need. 
Please could somebody help.

Thanks

Ian 

I think this will work     (UNTESTED) --
   qryLastTen --
SELECT  processing_grade, buy_price_alt_currency  
FROM t_worksheet
WHERE t_worksheet.collection_date IN(SELECT TOP 10 [XX].collection_date FROM 
t_worksheet AS [XX] WHERE [XX].processing_grade = 
t_worksheet.processing_grade ORDER BY [XX].collection_date) DESC;

SELECT processing_grade,  Sum([qryLastTen].buy_price_alt_currency)/10 AS 
Average_Price
FROM qryLastTen 
GROUP BY processing_grade;

-- 
Build a little, test a little.


"Ian C" wrote:

> I'm trying to create a query that gives me the average price of each product 
> based on the last 10 purchases. 
> 
> The table is called "t_worksheet", with fields called 
> "buy_price_alt_currency" which represents the price, "processing_grade" which 
> represents the product and "collection_date" which represents the purchase 
> date.
> 
> I've been struggling with sub queries but can't get the results I need. 
> Please could somebody help.
> 
> Thanks
> 
> Ian