I would lile to ask your advice on the following.
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 that say that 25+30 units of AAA1 was sold @ $120 and $130 respectively from inventory of 50 + 5 units purchased in the past @ $100 and $120 respectively. My task here is to assign Purchase Price to the 25 lot and 30 lot on FIFO bases, that is, first -in , first- out, and based on the prices assigned calculate Gains/Losses.
By this logic:
first lot of 25 would get $100,
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.
Have a Great Weekend,
All Canadians, Happy Canada Day for you tomorrow,