What does this mean, the most recent price in your recordset? For instance in the example data you would average the most recent day's data 6/14/2011? (average price, average Q and average dollars?)
Also, are you weighting your cost per item? For instance you have 15 items, if you were to average the price you would get something close to .33. However that's not really accurate if you had a huge volume of items at the .325 (which you do) price and a very small quantity at the .4 price (which you do) it should be weighted by the volume of items related to each price. in this case you'd have the sum of the price divided by the sum of the q to get the average price.
You also do not need to store the dollar column in your table, it can be calculated and is redundant.
so let's say your table containing this data is called Tbl_Test (and your DATE field is actually called ITEMDATE because date is a reserved word)
Create this query
Code:
SELECT Tbl_Test.Item, Max(Tbl_Test.ItemDate) AS MaxDate
FROM Tbl_Test
GROUP BY Tbl_Test.Item;
Call it Qry_Prelim
Create this query
Code:
SELECT Qry_Prelim.Item, Sum(IIf([itemdate]=[maxdate],[q],0)) AS MRDQuantity, Sum(IIf([itemdate]=[maxdate],[q]*[price],0)) AS MRDDollars, Sum(IIf([itemdate]=[maxdate],[q]*[price],0))/Sum(IIf([itemdate]=[maxdate],[q],0)) AS MRDAvgCost, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-7,[q],0)) AS MRWQuantity, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-7,[q]*[price],0)) AS MRWDollars, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-7,[q]*[price],0))/Sum(IIf([itemdate] Between [maxdate] And [maxdate]-7,[q],0)) AS MRWAvgCost, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-30,[q],0)) AS MRMQuantity, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-30,[q]*[price],0)) AS MRMDollars, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-30,[q]*[price],0))/Sum(IIf([itemdate] Between [maxdate] And [maxdate]-30,[q],0)) AS MRMAvgCost
FROM Qry_Prelim LEFT JOIN Tbl_Test ON Qry_Prelim.Item = Tbl_Test.Item
GROUP BY Qry_Prelim.Item;
My code assumes you want to figure the average most recent price based on the most recent date AND that the most recent cost is the total cost divided by the total units.
EDIT: my query is the most recent day, the most recent 7 days from the most recent date (going backwards, and the most recent 30 days from the most recent date (going backwards) you just have to change the [maxdate] - 30 to [maxdate] - <x> where x would be the number of days you want to go backward.