calculated field based on query

[samela101]

My setup:
Tables: Products, PurchaseDetails
I have a PurchaseOrder form which allows me to select products and add them to the purchaseDetails table. I would like to be able to view the average purchase price when adding them however I am not sure how to do this
My PurchaseOrder form is a multiple items form so I can add multiple products. When I try to add a query to display the average price paid it averages all the products prices, not the prices for each product paid previously

Example:
1/1/13 purchase
Apple Price Paid today: $1
Orange $1.5

1/2/13
Apple $2
Orange $2

what I'd like to do is:
New purchase
Apple avgPrice:1.50 Price Paid today: $1

Then next time the avgPrice would be (1+1.5+1)/3
I've tried to add a query to the form, but it shows the average price for the orange and apple

I was thinking I could add a calculated field in the product table based on the query of avgPrice paid for that ProductID, not sure if that is possible
If not perhaps I could do an afterUpdate Event on the purchase Table, I would want to run a query and set that result in the product table. I don't know how to run a query from there though, I am using macro tools, I don't really understand VBA so I'd rather steer away from that incase I need to tweek it in the future

Thanks for any help

[alansidman]

Why not add a subform that is based upon an aggregate query that is filtered (criteria) on the particular item you have selected in the main form.

Here is a link to building an aggregate query

calculated field based on query

[samela101]

I tried that but once I added the aggregate query to the source, the form no longer allows me to add records

[samela101]

Here's an example
FileSwap.com : test.mdb download free

[meratigoerr]

Try allen's suggestion and create a subform and use the aggrate query as the record source for that subform. You are correct, once you add an aggragate query to a recordsource it becomes non-updateable, but a subform would allow the main form to still be updateable.

[samela101]

That kind of works. I made two separate subforms, one updateable, one for the average. The one with the average does not update as I add new products though. The average is meant to be a reference so it needs to be able to update in real time otherwise its not very useful in that form.
I suppose I could do a refresh? or Requery? Not sure how to use either of those, i've been just calling the command close form then open form
Also, assuming I can get it to refresh each time, im having difficulty getting them to scroll together

Thanks for the help!

[samela101]

bump