Calculated field that references a cell in that row, and the entire field

[jwb1012]

Is it possible to reference a *cell* in a calculated field of access?

I want to add a column that takes the average of the "price" column, if the "product ID" column matches the "product ID" of that row, and the "date" column is less than the date in that row.

[TABLE="width: 523"]
<tbody>[TR]
[TD]No.
[/TD]
[TD]Product ID
[/TD]
[TD]Date
[/TD]
[TD]Price
[/TD]
[TD]*12-month Avg - Prior to this Purchase*
[/TD]
[TD]Calculation
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]1/1/2016
[/TD]
[TD] 10
[/TD]
[TD][/TD]
[TD]= 0
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]A
[/TD]
[TD]5/30/2016
[/TD]
[TD] 20
[/TD]
[TD]10
[/TD]
[TD]=AVG(1)
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]A
[/TD]
[TD]10/27/2016
[/TD]
[TD] 30
[/TD]
[TD]15
[/TD]
[TD]=AVG(2, 3)
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]A
[/TD]
[TD]3/26/2017
[/TD]
[TD] 40
[/TD]
[TD]25
[/TD]
[TD]=AVG(3, 4)
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Russell Hauf]

More information is needed, but check out the AVGIFS function.

Edit: sorry, AVERAGEIFS

 

[jwb1012]

Is it possible to use AVERAGEIFS in Access?

I have a table of purchases with: Product ID, Date, and Price...for each purchase, I need to add a calculated column that shows the average price for the 12 months prior to that purchase date.

 

[Micron]

That is an Excel function, and this is an Access question, no?
If the field you refer to is a table field, then you should not be storing calculated values in it. You can do calculations in a Totals query, averaging the price, Group By on Product ID, but I don't know what to suggest about your dates since it's not entirely clear to me what you want as results.

 

[jwb1012]

Hi Micron,

Yes, this is an Access question. I have moved away from trying to add a calculated field in the last column of my table and I am now creating a query. My table has a record of all purchases made (over a 5 year period). For each purchase, I need to determine what the "average price paid over the 12-month period prior to this purchase" was. So, if one record represents: the purchase of an apple, on 1/1/2017...I need to determine the "average price paid for an apple over the 12 months prior to 1/1/2017". This will allow me to compare the price I paid on 1/1/2017 to the average price I paid for an apple in the previous year. This average price could be made up of 100+ purchase scattered across different dates within that 12 month period.

 

[jwb1012]

But, I have many different products, so I cant explicitly refer to "apple" - I need to refer to the product in that row.

 

[jwb1012]

Okay, I have a new thought/approach. In my table "Table1", I have added a "start date" and "end date"... so in my query, I have the Product ID, start date, and end date. So all I want to do is create a query that pulls the average price for the product ID between those dates. This should be possible in access, right? I am veryexcel minded so I am having trouble wrapping my head around access.

 

[Micron]

Very possible, especially given the changes you made. The answer is in post#4. Totals query as suggested, but using your start and end date fields as WHERE parameters, not GroupBy or Aggregate function parameters. Either start with a query that includes your table and switch it to a Totals query (summation character in the ribbon) and choose these parameters from the combo selector in the Totals: row, or try the query wizard.