>Hi,
>I am developing Stock Valuation report, and use Weighted Average as cost calculation method.
>
>As what in my mind now is, I have to re-calculate the Weighted Average cost from FIRST day of using the system until AS AT date. It may take longer time to generate the report. It is still fine if only one product (~2-5 secs). However, if there are about 1000-10000 of product, then.. the report may take at least a day to be completed.
>
>I would like to get advice from anybody, how to resolve this problem? Any formula to get the Weighted Average cost without go thru each of the transaction from 1st day?
>
>Thank you for advice.
John, you need to create two running totals - one for the total amount of product bought and one for the total money spent to buy the product. You could create a table for this and it would contain fields like item_id, tot_bght, tot_cost. It would contain one record per product item. At any time you can calculate weighted cost per item by dividing tot_cost by tot_bght. When you create new/edit/delete transactions you will need to update this table appropiately.
hth
In the End, we will remember not the words of our enemies, but the silence of our friends - Martin Luther King, Jr.