Special average calculation

[mazides]

Hello,

i use access 2007 and i have a table with customer details and their purchases. I need a query to calculate the average value of the last 5 purchases per customer, irrespective of the day those purchases took place. That is, one customer may have made his last 5 purchases between lets say 15/09/13 and 25/09/13, while another may have made them one month earlier.

Thanks

 

[Roku]

Something like this will do what you need:

SELECT Avg(ctValue) AS AvgVolume FROM (SELECT TOP 5 ctValue FROM tCustomer WHERE ctId=2 ORDER BY ctDate DESC);

The inner query selects the top 5 items for customer 2 (in this case), from the most recently dated records. The outer query takes the average of these results.