December 13th, 2012, 02:00 PM
I have a database based on the template Desktop Product inventory database. It allows me to document purchases of products. I'd like to store the average price paid for each product.
If Price is updated
SumPrice = SumPrice + Price
TimesAveraged = TimesAveraged + 1
Average = SumPrice/TimesAveraged
SumPrice would be the sum of all the previous prices
I think access is having a problem with self reference? Any suggestions on another way?
December 13th, 2012, 02:51 PM
Is this supposed to be VBA code?? where are you trying to do this? Query? Form? Table?
Saving a calculated value is frowned upon. Calculated values may change if the values used to create them are altered, which would not be reflected in the stored value. Calculated values should be recalculated everytime you want to display them for the end user.
December 13th, 2012, 11:34 PM
this would be in a web form (no design mode), I was trying to use macro tools. Properties->Event->After Update (Embedded macro) ....
It doesn't necessarily have to be done that way. I would like for the average to be recalculated every time the stored value for Price is changed
12/10 Apples $0.20
Average price would be 0.20
12/13 Apples $0.50
Average price would now be 0.20+0.50/2
SumPrice= 0.70, TimesAveraged=2
12/14 Apples 0.30
SumPrice= 0.70+0.30=1.00, TimesAveraged=3
Average price is now 1.00/3=0.33
would I just need to use a temp variable?
December 14th, 2012, 09:06 AM
Changing the value everytime a new purchase is made is not a good way to go about it. It would be easier to store each new purchase as a new record in a table. Then use a simple aggregate(sp?) query to do your calculations.
Given the table [Purchases]:
ID ItemID Price PurchaseDate
1 1 .25 12/18/2012
2 1 .50 12/17/2012
3 1 .65 12/25/2012
the query :
would yeild results like
SELECT Purchases.ItemID, Sum(Purchases.Price) AS SumOfPrice, Avg(Purchases.Price) AS AvgOfPrice, Count(Purchases.Price) AS CountOfPrice
GROUP BY Purchases.ItemID;
ItemID / SumOfPrice / AvgOfPrice / CountOfPrice
1 / 1.4 / 0.466666666666667 / 3
December 15th, 2012, 08:24 PM
So my current set up is:
Open up (form) Product Detail, click a (subform) tab and the purchase history for that product is listed
Limit master fields: ID
Limit child fields: ProductID
that is from the template desktop Product inventory database, then in that same window I'd like to average the prices paid for those purchases, I wasn't sure how to do that so I made a new query (which I'm sure isn't necessary, since like I said it already lists them), limited to the product that is selected, and made a report based on that, which has the averaged price. It works but is pretty redundant. Can't I do something based on what the template already has?