VB and Access

Hello All,
              I would lile to ask you about the following today.

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
AutofreakAsked:
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.

heer2351Commented:
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.
AutofreakAuthor 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
ACSIPaulCommented:
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
                Else
                    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
                 
                buyRS.MoveNext
            Loop
           
            buyRS.Close
            Set buyRS = Nothing
           
           
            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

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
AutofreakAuthor Commented:
Paul,
          this one is also part of the Q you answered so why leave it for cancelation, Thanks! Take care! Serge

http://www.experts-exchange.com/Databases/MS_Access/Q_21476953.html
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.