>>Dear Sir/Madam
>>
>>I have a purchase List Table Eg.
>>
>
>>ArrivalDate Qty Rate Total Value
>>22-May-10 2999.984 $805.00 $24,29,568.53
>>20-Apr-10 1250.000 $819.00 $10,27,140.30
>>11-Apr-10 170.000 $800.00 $1,36,000.00
>>11-Apr-10 1500.277 $800.00 $12,04,398.61
>>08-Apr-10 1334.341 $800.00 $10,70,842.95
>>08-Apr-10 1500.000 $810.00 $12,18,802.06
>>03-Apr-10 500.000 $790.00 $3,96,212.85
>>03-Apr-10 2499.664 $790.00 $19,80,622.10
>>29-Mar-10 700.000 $805.00 $5,64,989.05
>>08-Feb-10 1499.912 $768.00 $11,58,486.82
>>08-Feb-10 1500.000 $787.00 $11,80,500.00
>>
>
>>
>>I need a query if user input 5000 then
>>
>>Result should be ($24,29,568.53 + $10,27,140.30 + $1,36,000.00 ) + (581 (Balance amount) * 800)= 464800
>>
>>So Total amount is 4057508.83
>>
>>If we divide the above amount by 5000 I get Avg pur rate of my unsold Stock (811.5) .
>>
>>My table is Sorted desc
>>
>>Even if more than one Query is required please help me to solve it.
>
>
>1) I have a table with Purchase information Date of Purchase, Qty Purchased , and Rate
>
>2) Now lets say I have 5000 MT in Stock
>
>3) I need purchase average price for 5000 MT
>
>4) So I will take the latest purchase first which is 2999.984 and mulitply it by 805.
>then 1250 * 819 and add it to previous total and keep a record of Qty running total
>and continue till my qty total reached 5000 MT
>
>5) After three rows Qty total is 4420
>
>6) so on forout row I will take only 580 * 800
>
>7) so now I have my Total purchase value for 5000 MT and it I divide it by 5000 I will get the Purchase Avergae

I see. If it would be a SQL Server, then there are many different solutions available from looping in cursor to quirky update.

Looks like in Access the running total is incorporated into the language.

Take a look at http://support.microsoft.com/kb/290136/en-us

and alternative way (Query only)

http://articles.techrepublic.com.com/5100-10878_11-6140569.html