# SQL query vs. VB??

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 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,

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:
Serge:
On first glance, I cannot see how you can avoid using VBA.  i am wondering why the purchase and sale of the items does not trigger some action when the data is input.
but, while you enjoy Canada Day, tomorrow, I am sure others will provide better insight.
Hey, do you guys have Fourth of July up there?
Author Commented:
What is Fourth of July?  other then just another Monday and another month beginning?
Commented:
Well, it is the day after Third of July, eh?
But you are right, Canada does happen to be about the only Commonwealth country whose ass we haven't kicked.
Commented:
I apologize.  Sorry for the comment......
Author Commented:
It's OK, I am not a hard core nationalist,
So, who are You, who kicked almost every ass in CW :) I presume our mighty nabour from the South, right?

Take care.
Serge
Author Commented:
By the way,
did you know that Canadians envaded the US during the war and reached as far as Washington, we actually burned the white house down to the ground, how is that for ass kicking :)))

Cheers
Commented:
Is there a date field to prevent units being sold before they are purchased.
For real time operating see jerryb30.
If you want to 'do' the entire database i suspect grouping and sorting the purchases then sorting the sales then using the queries as recordsets would allow VB to work quickly

Commented:
jerryb30: I invite you to name one in this century or the last.
Author Commented:
DavidW,
could you please be more specific.
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