I would lile to ask you about the following today.
Say, there's a table, table1 in myDB.mdb that liiks like following:
Security Type Units Purchase Price Sale Price Gains/Loss
AAA1 B 50 100 - -
AAA1 B 5 120 - -
AAA1 S 25 ? 120 ?
AAA1 S 30 ? 130 ?
Thes are four trading records above that say that 25+30 units, Type "S", ( 3rd and 4th lines) were sold @ $120 and $130 respectively from inventory of 50 + 5 units(1st and 2nd lines) purchased in the past @ $100 and $120 respectively. My task here is to assign Purchase Price to the 25 lot (3rd line) and 30 lot (4th line) on FIFO bases, that is, first -in , first- out, and based on the prices assigned calculate Gains/Losses.
FIFI logic would result in this :
first lot of 25 (3rd line) would get $100 price from 1st Type B line for all 25 units ,
G/L = ($120-$100)*25=$500
2nd lot of 30 would get 25 units @ $100 and 5 @120
G/L = ($130-$100)*25 +($130-$120)*5 = $800
While the logic in this case is pretty simple, I am not sure if I can implement it more effectively through VB, or SQL ,or some combination of both. The file is usually huge and pure VB looping and record-by-record checking is very time consuming. I learned the power of SQL recently and hoped you would prbably be able to help me.
Thank you very much