February 12th, 2013, 04:01 AM
calculated field based on query
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
Apple Price Paid today: $1
what I'd like to do is:
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
February 12th, 2013, 12:15 PM
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
Office 2010, 2007, 2002
If I helped you, then click "give rep" button in the lower left corner.
February 13th, 2013, 12:59 AM
I tried that but once I added the aggregate query to the source, the form no longer allows me to add records
February 13th, 2013, 01:30 AM
February 13th, 2013, 10:06 AM
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.
February 15th, 2013, 10:33 AM
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!
Last edited by samela101; February 15th, 2013 at 10:55 AM.
February 19th, 2013, 05:00 PM