Calculate Average Cost in Access

[cafirf]

Hi,



I have a stock porfolio which I am trying to control using access instead of Excel. I have a table with the daily purchase and sale of stocks and I am trying to calculate the overall average cost of the entire portfolio. The calculation methodology is the same as for the Accounting Average Cost Inventory calculation



I pasted a simple example of the calculation I want to make in Access:
first 4 columns are from tb_movement - it has the daily portfolio purchase and sale
[Notional] = [qtty]*[price]
[Average Cost] - this is what I need to calculate. it is the sum of the notional divided by the sum of quantity for all purchase until the I have a sale, where the price should be the last average cost value multiplied by the quantity sold.

Day	Type of Transaction	Qtty	Price	Notional	Average Cost
1	Purchase	5	130	650	130,00
2	Purchase	10	108	1080	115,33
3	Purchase	11	130	1430	121,54
4	Purchase	13	130	1690	124,36
5	Purchase	7	108	756	121,87
6	Sale	-11	121,87	-1340,57	121,87
7	Purchase	15	127	1905	123,41
8	Purchase	14	121	1694	122,88
9	Purchase	8	118	944	122,34
10	Sale	-9	122,34	-1101,05	122,34

hope it wasn´t too confusing, sorry i´m new to access and I can´t figure out how to do that.
thank you!

[June7]

Your example shows a running average. This is not simple to do in a query. It is relatively simple in a report because textbox has RunningSum property. Running calc is a common topic. Search forum or web.

Your requirement to show in a Sale record the average value up to that point is like a 'year-to-date' summation. Review http://allenbrowne.com/subquery-01.html#YTD