get average in access query

Question

Hi, I want to see the item only the last time that I bought one in this query so I get the max of the year and month, like it's showing in the first pic and this works just fine but average field I don't know why it's still calculate all not just the last year and month but event though it shows the last item right how to make it show only the prices in this month

I tried it this [AdditionPermissions!][additiondate]=Max(Year([AdditionDate])) and Max(Month([AdditionDate])) as a criteria but it's not working it show me the below error message

Answer 1

Are you saying you want to return the average price of each item in the latest month when the item was purchased?  If so try this:

SELECT Items.ItemID, ItemName, FORMAT(AdditionDate,"mmmm yyyy") AS LatestMonthPurchased,

AVG(Price) As AveragePrice

FROM AdditionPermissions as AP1 INNER JOIN Items

ON  AP1.ItemID = Items.ItemID

WHERE FORMAT(AdditionDate,"yyyymm") =

    (SELECT MAX(FORMAT(AdditionDate,"yyyymm"))

      FROM AdditionPermissions as AP2

      WHERE AP2.ItemID = AP1.ItemID)

GROUP BY Items.ItemID, ItemName, FORMAT(AdditionDate,"mmmm yyyy");

The outer query is restricted by the subquery, which is correlated on ItemID, to the MAX (latest) year/month when the item was purchased, and consequently returns the average price paid in that year/month.

Answer 2

Now you need two queries:

First only selects the max date per item. There is no need to split year and month:

select itemID, max(AdditionDate)

from AdditionPermissions

group by itemID;

(let's call this query qryMaxAdditionDate)

Then you create a second query selecting the first query and the table(s) you need, and you join with both ItemID and MaxOfAdditionDate, and can then pick up other fields you are interested in.

Answer 3

Your AdditionPermissions table is incorrect: it should have ItemID, not ItemName.

Then enforce the relation between those two tables.

Fix that first and then we can talk again.

Answer 4

I did what you said now what

Answer 5

thanx a lot it works