Calculating an average cost to update master file 1

[k9handler]

I'm new at designing access databases and need some help with a query. I'm using Access 2010 but saving database as Access 2007 format due to some end user restrictions. I've created an inventory database and need to run a query to calculate the average cost on an inventory item and then update the item master file with the average cost, most recent cost, and the last purchase date.
Using the AVG total function on the price field calculates an average, but not the correct average. Below is some sample data (records in transaction table)

1st purchase 22 units at $284.40 for a total of $6,256.80
2nd purchase 25 units at $281.93 for a total of $7,048.25
3rd purchase 40 units at $252.87 for a total of $10,114.80
Total of purchases = 87 units for a total of $23,419.85

AVG function on unit price results in 273.07. The correct average price I need is 23,419.85 divided by 87 units for an average unit price of 269.19.

I then need to update the master file for the average price of 269.19, the last cost of 252.87 and the last purchase date of the 3rd purchase.

Help would be much appreciated.

 

[PHV]

For the average I'd try something like this:
SELECT [inventory item], SUM([unit price]*Quantity)/SUM(Quantity) AS AveragePrice
FROM yourTable
GROUP BY [inventory item]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[k9handler]

That worked - thanks for the help