I have a Transaction table with fund c(6), amount Y, and lot C(10). They are in date order but date doesn't matter for example. There are also multiple funds but again, for simplicity.

Fund is a fund code. Amount is how much fund in transaction + is buy, - is sell.

For a purchase, increment the lot# and store in the table with its transaction.
For a sale, the lot number needs to show which lot or portions of lot were sold. FIFO style. Therefore Lot could be 2,3 or 2,3,4 etc.

ORIGINAL INFO
FUND AMOUNT LOT
AAA 1000
AAA 1000
AAA - 500
AAA 50
AAA -2050


RESULT TABLE
FUND AMOUNT LOT
AAA 1000 1
AAA 1000 2
AAA - 500 1
AAA 50 3
AAA -1550 1,2,3

All I need to do is store the lot(s) in the original table.


I'm looking for a simple logical way to do this. Speed is not primary but woud be considered. Arrays or working table is my though.
Any ideas?