SQL query vs. VB??

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


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.

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?
AutofreakAuthor Commented:
What is Fourth of July?  other then just another Monday and another month beginning?
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.  
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

I apologize.  Sorry for the comment......
AutofreakAuthor 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.
AutofreakAuthor 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 :)))

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

jerryb30: I invite you to name one in this century or the last.
AutofreakAuthor Commented:
             could you please be more specific.
Thank you,
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 buyUnits, sellUnits As Integer
    Dim sprice, pprice, GLTotal As Currency
    Dim buysum, prevsum, remainingunits, buyID, sellID As Integer
    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
            Set buyRS = db.OpenRecordset(buySQL)
            buysum = 0
            prevsum = 0
            done = False
            GLTotal = 0
            Do While (Not buyRS.EOF And Not done)
                buyID = buyRS("id")
                buyUnits = buyRS("units")
                remainingunits = buyRS("remaining_units")
                pprice = buyRS("pprice")
                prevsum = buysum
                buysum = buysum + remainingunits
                If (buysum <= sellUnits) Then
                    GLTotal = GLTotal + (remainingunits * pprice)
                    db.Execute "Update Trans set remaining_units = 0 where id = " & CStr(buyID)
                    If (buysum = sellUnits) Then
                        done = True
                        db.Execute "update Trans set GL = " & CStr((sellUnits * sprice) - GLTotal) & " where id = " & FormatNumber(sellID, 0, , , vbFalse)
                    End If
                    remainingunits = buysum - sellUnits
                    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
            Set buyRS = Nothing
        Set sellRS = Nothing
    Set securityRS = Nothing

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.