VB and Access

Hello All,

Say, there's a table, table1 in myDB.mdb that liiks like following:

table1:

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

likewise,

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
Serge
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Number of things are not clear in this question:

1) How do you know that type B and S are related.
2) Tables do not have a record order, so FIFO can only work if for example a date is added to the table. Date when purchases/sold.
3) What needs to happen to the table after the math?

Once you have sold the previously purchased goods you need to update the stock.
Author Commented:
> 1) How do you know that type B and S are related.

If there's (an) S record(s) there is always (a) B type(s) to offset, thier totals equal

> 2) Tables do not have a record order, so FIFO can only work if for example a date is added to the table. Date when purchases/sold.

You are right, there is a data field that together with Security # uniquely id B's and S's

> 3) a G/L report is generated

Security       Type      Units     Purchase Price        Sale Price      Gains/Loss
AAA1             S           25              100                       120             500
AAA1             S           25              100                       130             750
AAA1             S            5               120                       130             50

or the consolidated form

Security       Type      Units     Purchase Price        Sale Price      Gains/Loss
AAA1             S           25              100.0000               120             500
AAA1             S           30              103.3333               130             800

Thank you,
Serge
Commented:
This should work.

Adding an autonumber counter (id) to the table and a remaining_units to the table causes the following code to work for your puzzle.

Dim db As Database
Dim securityRS, buyRS, sellRS As Variant ' recordsets
Dim securitySQL, buySQL, sellSQL As String

Dim security As String
Dim sprice, pprice, GLTotal As Currency

Dim done As Boolean

done = False

Set db = CurrentDb
securitySQL = " select distinct security " & _
" from Trans " & _
" where type = ""Sell"" " & _
"   and GL = 0 "

sellSQL = " select id, units, sprice " & _
" from Trans " & _
" where security = @SECURITY " & _
"   and type = ""Sell"" " & _
" order by id "

buySQL = " select id, units, remaining_units, pprice " & _
" from Trans " & _
" where security = @SECURITY " & _
"   and remaining_units > 0 " & _
" order by id "

Set securityRS = db.OpenRecordset(securitySQL)

Do While (Not securityRS.EOF)
security = securityRS("security")

sellSQL = Replace(sellSQL, "@SECURITY", """" & security & """") ' replace @SECURITY with the actual security in sell query
Set sellRS = db.OpenRecordset(sellSQL)

Do While (Not sellRS.EOF)
sellID = sellRS("id")
sellUnits = sellRS("units")
sprice = sellRS("sprice")

buySQL = Replace(buySQL, "@SECURITY", """" & security & """") ' replace @SECURITY with the actual security in buy query

prevsum = 0
done = False
GLTotal = 0

Do While (Not buyRS.EOF And Not done)

GLTotal = GLTotal + (remainingunits * pprice)

db.Execute "Update Trans set remaining_units = 0 where id = " & CStr(buyID)
done = True
db.Execute "update Trans set GL = " & CStr((sellUnits * sprice) - GLTotal) & " where id = " & FormatNumber(sellID, 0, , , vbFalse)
End If
Else
GLTotal = ((buyUnits - remainingunits) * pprice) + GLTotal
db.Execute "update Trans set remaining_units = " & FormatNumber(remainingunits, 0, , , vbFalse) & " where id = " & FormatNumber(buyID, 0, , , vbFalse)
db.Execute "update Trans set GL = " & CStr((sellUnits * sprice) - GLTotal) & " where id = " & FormatNumber(sellID, 0, , , vbFalse)
done = True
End If

Loop

sellRS.MoveNext
Loop

sellRS.Close
Set sellRS = Nothing

securityRS.MoveNext
Loop

securityRS.Close
Set securityRS = Nothing

bbfn,
Paul.

Experts Exchange Solution brought to you by