record manipulation puzzle

Hello Experts,
                        I am surprised to learn that my questions about inventory management turned up to be such a challenge for You,  coz I thought those were pretty straightforward and common. I will try to approach this from a different angle and may be we will be able to work sth out together.

my questions:
http://www.experts-exchange.com/Databases/MS_Access/Q_21482334.html
http://www.experts-exchange.com/Databases/MS_Access/Q_21476953.html     

                 
                        The problem I am dealing with is really simple and had it been a small data sample I would have already dived in and coded the logic. But since there are tens of thousands records I have to make a strategic decision and thought that this forum was the place to go to for advise.  

                         All I need to do is to allocate inventory units to sold batches on FIFO basis. I have provided the samples in my questions. Now, if you can't come up with the total solution, maybe  you can point the direction, dos and donts, so that I don't go in circles.

                         Let me ask you this. Let's compare three data manipulation means : Excel spreadsheet, VB array, Access table. Say, we have this data set

  Transaction                     Units           Purchase price               Selling Price
        Buy                              3                     100                              -
        Buy                             5                      200                              -
         Sell                            4                       ?                                300
         Sell                            4                       ?                                500


Which of the three means would you use to solve for the unknown purchase prices?

Thank you!
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.

Eric ShermanAccountant/DeveloperCommented:
I would use an Access table that includes a "Remaining Units" field that is refreshed after each Sell transaction relieving inventory using the FIFO method.

ET
ACSIPaulCommented:
Here's the table I used for this:

create table Trans
(
   id  int identity(1,1) Primary key,
   dt  datetime default (getdate()),
   security      varchar(10),
   type   varchar(4),
   units  int,
   remaining_units int,
   Pprice  float,
   Sprice  float
)
go

where Pprice is the purchase price and Sprice is the sale price.  I also added a "remaining_units" field that will get updated after processing each Sell record.  The Security field is your Security #, which I'm assuming uniquely identifies each group of Buys and Sells, i.e., keeps them separate from the other Buys and Sells of a different stock/security.

I added an ID field as an autonumber field (in SQL Server: identity(1,1)) to guarantee the FIFO order.  I added a date for the same reason, but as luck would have it, I inserted the records so fast that the date didn't change between the records.  This points out a possible problem with using the date to guarantee FIFO order.  So, while its here, I didn't use it.

I created a trigger on this Trans table to populate the Remaining_Units field, since technically, I assumed that forcing the Remaining_Units to be populated by your own current inserts would force a change in that code....so to alleviate that problem, I used a trigger:

create trigger xyzTr on Trans for insert
as
update Trans
  set remaining_units = units
where type = 'Buy'

update Trans
  set remaining_units = 0
where type = 'Sell'
go

The second Update statement is not needed, since remaining_units is meaningless to a Sell record.

Then I created a stored procedure to calculate all of the Gains/Losses, inside of SQL Server, which I have not tested, yet, because my brain hurts, now ;-)

Create Procedure CalculateGL
as
declare distinctSecuritys cursor for select distinct security from Trans order by id

declare @security  varchar(10)
declare @units     int
declare @pprice    float
declare @sprice    float
declare @buySum    int
declare @prevSum   int
declare @ssum      int
declare @BUYid     int
declare @SELLid    int
declare @remainingunits int
declare @GLTotalPurchasePrice float

open distinctSecuritys

fetch next distinctSecuritys into @security

while @@FETCH_NEXT = 0 BEGIN
   declare SELLtransRecords cursor for
       select id, units, sprice from Trans where security = @security and type = 'Sell' order by id
   open SELLtransRecords
   
   fetch next SELLtransRecords into @SELLid, @units, @sprice

   While @@FETCH_NEXT = 0 BEGIN
      declare BUYtransRecords cursor for
         select id, remaining_units, pprice from Trans where security = @security and remaining_units > 0 order by id
      open BUYtransRecords

      fetch next BUYtransRecords into @BUYid, @remaining_units, @pprice
      set @ssum = 0
      set @buysum = 0
      set @prevsum = 0
      set @done = 0
      While @@FETCH_NEXT = 0 and @done = 0 BEGIN
         if (@buySum <= @units) begin
            set @prevsum = @buysum
            set @buysum = @buysum + @remaining_units
            if @buysum > @units set @remainingunits = @buysum - @prevsum
            else set @remainingunits = 0
            update Trans set remaining_units = @remainingunits where id = @BUYid
            set @GLTotalPurchasePrice = @GLTotalPurchasePrice + ((@units - @remainingunits) * @pprice)
                               end
         else BEGIN
            update Trans set GL = @GLTotalPurchasePrice - (@units * @sprice) where id = @SELLid
            set @done = 1
              END
         fetch next BUYtransRecords into @BUYid, @remaining_units, @pprice
      END
      close BUYtransRecords
      Deallocate BUYtransRecords

      fetch next SELLtransRecords into @SELLid, @units, @sprice
   END
   close SELLtransRecords
   Deallocate SELLtransRecords
   
   fetch next distinctSecuritys into @security
end

close distinctSecuritys
Deallocate distinctSecuritys
go

Generally, I think I've got the right process, but I might have missed a step or two, or goofed on some of the syntax.  But I wanted to go ahead and post this to get you thinking down the right path.

bbfn,
Paul.

ACSIPaulCommented:
Incidently, if you do not have the ability to add an ID column, and a remaining_units column to your existing data table, then you could create a separate, temporary table that has these columns and copy the data to the temporary table during processing.  The difficulty will happen when you want to copy the Gain/Loss calculation back into the original table.  Matching up the Sell record in one table with the Sell record in the other table will be difficult.

*********
Also, as I look at this, my sample "Create Table" statement at the top, is missing the GL field to hold the Gain/Loss value, which is updated near the bottom of the stored procedure.
*********

To run this procedure, you simply need to make a call to:
    EXEC CalculateGL

bbfn,
Paul.
Your Guide to Achieving IT Business Success

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.

ACSIPaulCommented:
Okay...ton of syntax errors.

Here's a replacement stored procedure with no syntax errors.


Create Procedure CalculateGL
as
declare distinctSecuritys cursor for select distinct security from Trans

declare @security  varchar(10)
declare @units     int
declare @pprice    float
declare @sprice    float
declare @buySum    int
declare @prevSum   int
declare @ssum      int
declare @BUYid     int
declare @SELLid    int
declare @remainingunits int
declare @GLTotalPurchasePrice float
declare @done      int

open distinctSecuritys

fetch next from distinctSecuritys into @security

while @@FETCH_STATUS = 0 BEGIN
   declare SELLtransRecords cursor for
     select id, units, sprice from Trans where security = @security and type = 'Sell' order by id
   open SELLtransRecords
   
   fetch next from SELLtransRecords into @SELLid, @units, @sprice

   While @@FETCH_STATUS = 0 BEGIN
      declare BUYtransRecords cursor for
         select id, remaining_units, pprice from Trans where security = @security and remaining_units > 0 order by id
      open BUYtransRecords

      fetch next from BUYtransRecords into @BUYid, @remainingunits, @pprice
      set @ssum = 0
      set @buysum = 0
      set @prevsum = 0
      set @done = 0
      While @@FETCH_STATUS = 0 and @done = 0 BEGIN
         if (@buySum <= @units) begin
            set @prevsum = @buysum
            set @buysum = @buysum + @remainingunits
            if @buysum > @units set @remainingunits = @buysum - @prevsum
            else set @remainingunits = 0
            update Trans set remaining_units = @remainingunits where id = @BUYid
            set @GLTotalPurchasePrice = @GLTotalPurchasePrice + ((@units - @remainingunits) * @pprice)
                               end
         else BEGIN
            update Trans set GL = @GLTotalPurchasePrice - (@units * @sprice) where id = @SELLid
            set @done = 1
              END
         fetch next from BUYtransRecords into @BUYid, @remainingunits, @pprice
      END
      close BUYtransRecords
      Deallocate BUYtransRecords

      fetch next from SELLtransRecords into @SELLid, @units, @sprice
   END
   close SELLtransRecords
   Deallocate SELLtransRecords
   
   fetch next from distinctSecuritys into @security
end

close distinctSecuritys
Deallocate distinctSecuritys
go

bbfn,
Paul.
ACSIPaulCommented:
Okay, once more with feeling!  Ignore those lengthy procedure posts above....too many errors.

Syntax errors gone.  I tested this stored procedure.  Based upon how I understand your problem, this stored procedure WORKS!  I have no idea how fast or how slow it will perform on a table with 10's of thousands of records.  I just know that it works.  I included the correct table definition below the procedure, and the test data that I used to test it with.

Create Procedure CalculateGL
as
declare @security  varchar(10)
declare @SELLunits     int
declare @pprice    float
declare @sprice    float
declare @buySum    int
declare @prevSum   int
declare @ssum      int
declare @BUYid     int
declare @SELLid    int
declare @remainingunits int
declare @GLTotal float
declare @done      int
declare @msg         varchar(255)
declare @BUYUnits  int

declare distinctSecuritys cursor for select distinct security from Trans

open distinctSecuritys

fetch next from distinctSecuritys into @security

while @@FETCH_STATUS = 0 BEGIN
   declare SELLtransRecords cursor for
     select id, units, sprice
     from Trans
     where security = @security and type = 'Sell'
     order by id
   open SELLtransRecords
   
   fetch next from SELLtransRecords into @SELLid, @SELLunits, @sprice

   While @@FETCH_STATUS = 0 BEGIN
      declare BUYtransRecords cursor for
         select id, units, remaining_units, pprice
         from Trans
         where security = @security and remaining_units > 0
         order by id
      open BUYtransRecords

      fetch next from BUYtransRecords into @BUYid, @BUYUnits, @remainingunits, @pprice
      set @ssum = 0
      set @buysum = 0
      set @prevsum = 0
      set @done = 0
      set @GLTotal = 0
      While @@FETCH_STATUS = 0 and @done = 0 BEGIN
           set @prevsum = @buysum
           set @buysum = @buysum + @remainingunits
         if @buySum <= @SELLunits begin
            set @GLTotal = @GLTotal + (@remainingunits * @pprice)
          update Trans set remaining_units = 0 where id = @BUYid
            if (@buysum = @SELLunits) begin
               set @done = 1
               update Trans set GL = (@SELLunits * @sprice) - @GLTotal where id = @SELLid
                                      end
                               end
         else BEGIN /* @buysum > @SELLunits */
            set @remainingunits = @buysum - @SELLunits
            set @GLTotal = (@BUYunits - @remainingunits) * @pprice + @GLTotal
            update Trans set remaining_units = @remainingunits where id = @BUYid
           

            update Trans set GL = (@SELLunits * @sprice) - @GLTotal where id = @SELLid
            set @done = 1
              END
         fetch next from BUYtransRecords into @BUYid, @BUYUnits, @remainingunits, @pprice
      END
      close BUYtransRecords
      Deallocate BUYtransRecords

      fetch next from SELLtransRecords into @SELLid, @SELLunits, @sprice
   END
   close SELLtransRecords
   Deallocate SELLtransRecords
   
   fetch next from distinctSecuritys into @security
end

close distinctSecuritys
Deallocate distinctSecuritys
go


I used this table and trigger:
create table Trans
(
   id  int identity(1,1) Primary key,
   security      varchar(10),
   type   varchar(4),
   units  int,
   remaining_units int,
   Pprice  float,
   Sprice  float,
   GL      float default (0)
)
go
create trigger xyzTr on Trans for insert
as
update Trans
  set remaining_units = units
where type = 'Buy'

update Trans
  set remaining_units = 0
where type = 'Sell'
go

And I used this data to test with:
insert into Trans (security, type, units, Pprice, Sprice) values ('AAA1', 'Buy', 2, 100.00, 0)
insert into Trans (security, type, units, Pprice, Sprice) values ('AAA1', 'Buy', 2, 200.00, 0)
insert into Trans (security, type, units, Pprice, Sprice) values ('AAA1', 'Buy', 2, 300.00, 0)
insert into Trans (security, type, units, Pprice, Sprice) values ('AAA1', 'Buy', 2, 400.00, 0)
insert into Trans (security, type, units, Pprice, Sprice) values ('AAA1', 'Buy', 2, 500.00, 0)
insert into Trans (security, type, units, Pprice, Sprice) values ('AAA1', 'Sell', 4, 0, 300.00)
insert into Trans (security, type, units, Pprice, Sprice) values ('AAA1', 'Sell', 4, 0, 600.00)
go

I tried changing the numbers for buys, and sells, and the Gains/Losses always came out the same as when I manually calculated it!!

Now I just hope that this is what you were trying to do!

bbfn,
Paul.
AutofreakAuthor Commented:
Hello Paul,
                I am sorry to read that this caused your brain to hirt at somne point and thank you for all the effort, I really appreciate it. Is this code C++ or Java, or sth else. Problem is I only have experience with VB.  Would it be possible to translate your work into VB?

Regards
Serge  
jerryb30Commented:
Serge:

Thought about your question last night, and scribbled some VB, which I forgot to bring with me.  Monday.

Paul's solution, I think, assumes SQL Server, or some other database supporting stored procedres.  Which is the way to go, one you have all of your current SELLS and BUYS aligned.  If the tranactions are in Access, then a similar process can be done on some event like after update.

The process requires having the abillity to track transaction date/time, plus how many units have been bought/sold out of a lot.  
So, the ability to add a field is also critical.

As far as it being an easy question-As you can tell from Paul, juggling the logic of the full process causes pain.

ACSIPaulCommented:
Hi Serge,

I'm sorry, I inferred from one of your earlier posts that you had discovered the power of SQL, so I wrote it in SQL, albeit SQL Server's flavor of SQL.  I should have revisited your other questions again before I spent the time writing it in Transact SQL.

The code above will work on SQL Server, but not in Access.  In access the code will have to be in VBA.  You will need 3 SQL queries and corresponding recordsets.  The first to walk the distinct list of Securities.  The second to process the Sell records, and the third to process the BUY records.  The code should be a direct port to VBA.

I will rewrite it in VBA for you.

bbfn,
Paul.


AutofreakAuthor Commented:
Thanks Paul and Jerryb30,  Sorry Paul for not specifying explicitely the tool I work with,  I will be switching to SQL Server very soon so you work is not going to be vaisted :).  

    I also wanted to add this comment coz I suspect my explanation of the problem is a bit confusing in part of the problem's dinamics. I am not writing an inventory management tool that allocates the purchase price to the sold unites as they hit the database. I actually recieve an CSV file with daily sales in it . But the report also has purchases in it to provide the information for gains/Loss calculation.  So i think it does not need any event triggering that I sense in some solution you guys are suggesting, it's a purely one-time report processin procedure./


Thank you
Serge
ACSIPaulCommented:
Okay, Serge....

...here's the VBA translation.  I haven't had time to test this, and won't for several hours.  I've got to run off to a meeting.  However, if you know VB, then you should be able to paste this code into an Access code window, into the appropriate function and get it to work.

If you have further difficulty, I will test and clean out syntax errors or other things I've overlooked later today.

Here's the code.

    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 = rs("security")
       
        Replace sellSQL, "@SECURITY", """" & security & """" ' replace @SECURITY with the actual security in sell query
        Set sellRS = db.OpenRecordset(sellSQL)
       
        Do While (Not sellRS.EOF And Not done)
            sellID = rs("id")
            sellUnits = rs("units")
            sprice = rs("sprice")
           
            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)
                buyID = rs("id")
                buyUnits = rs("units")
                remainingunits = rs("remaining_units")
                pprice = rs("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 = (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.
ACSIPaulCommented:
ugh, it wrapped!
ACSIPaulCommented:
Bug:

First query....

    securitySQL = " select distinct security " & _
                  " from Trans " & _
                  " where type = ""Sell"" " & _
                  "   and GL <> 0 "

Last line should be:
                  "   and GL = 0 "

This allows you to run the procedure multiple times without having to do recalculate all of the records again.  By limiting to only the "Sell" records, the query takes less time to build the distinct list, also it won't try to process Buy records for which there are no Sell records.....although the way I wrote this, that's not going to happen anyway.

bbfn,
Paul.
AutofreakAuthor Commented:
This is awesome!!   I am going to work on it soon.
I think you can post the answer as well to

http://www.experts-exchange.com/Databases/MS_Access/Q_21482334.html
http://www.experts-exchange.com/Databases/MS_Access/Q_21476953.html     

and get the jackpot .-)

Appreciate that,
Serge
ACSIPaulCommented:
Cool!  Glad I could help!

I've tested it.  There were some bugs in the VBA code.  I fixed them.  This one works.

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:
Hello Paul,
                 Can you please send me the Access table you tried the code with, I am having difficulty understanding things, too advanced for me , but being able to loop thru the code will help me to get it faster. Thing is, the real problem is a little more advanced and without comprehending your solution for the basic model I will not be able to customise it to the real thing. My email :  sergai.vasilyeu@td.com

Thank you,
Serge



ACSIPaulCommented:
Hi Serge,

It is on the way.

bbfn,
Paul.
AutofreakAuthor Commented:
Hello Paul,
                 sorry, could not work on it , got to prepare for my vacation - going away for a month on Tuesday. I am going thru the code right now and will let you know if there are any questions. I will mark the answers befor I go for sure. Thanks a lot!
Serge
AutofreakAuthor Commented:
Paul,
         this is the most elegant solution I ve ever seen, and coded fantastically too. Excellent job, and isn't all that complicated - nice combination of SQL and VB.

        I need to ask you this qiestion. In the table you provided the order for both buys and sells is a given - basically ascending by price - buys and sells . But what if the original CSV file has sales and buys in no order and they get imported unsorted into the database? Plus I have some inventory logic to implement.

         Say, I am using FIFO. There's an additional date field  - Date_of_purchase in "Trans" table. Each Buy record has a date of purchase and before I put the buys into BuySQL query I need to make sure they get there in a historical order sp that I could assign the right inventory units to the sales transactions.  
         
       The  sales order also has a rule - acsending by "sprice" - the cheapest batch goes first. Date is of no consequence for sales.    


Thank you,
Serge
ACSIPaulCommented:
Thanks!

I used ascending price, but I could have used random prices -- it would have worked just as well.  The ID field guarantees the order, not sprice.  The order that the buys and sells appear in, is basically not an issue, so long as you cannot sell a security before you buy it.  In other words, this is not valid:


A1     Buy           4            $10
A1     Sell           2            $15
B1     Buy            2             $10
A1     Buy           9             $5
A1     Sell           4             $10
A1     Buy           9             $7
A1     Sell          40            $20
B1     Sell           8             $20

Since the code processes first by security, second by Sell, and third by buy, the only order that is required is that there must be a way to determine which items were first vs. which items were next.....i.e., in order to process in FIFO order, you must be able to determine which ones are first.

If you use a date field instead of the autonumber field, you run into the problem where the interval between records being inserted is smaller than the smallest interval that can be represented by the clock.  In my first test, I quickly discovered that I had 3 records entered with the exact same date.  The first thing you learn about a relational database is that the stored order of the data is not guaranteed by the database engine.  Hence, you must create an artificial value which guarantees the order of your data.  An autonumber field is ideal for this situation.

Does this answer your question?

bbfn,
Paul.

P.S. A month vacation?  Sounds like fun!  I haven't had a vacation in 7 years longer than 36 hours.
AutofreakAuthor Commented:
Oh, you posted it here, sorry I confused myself, So Yeah, I will have lots of fun, for sure. Thanks for your help , it opened a new perspective for me in developing stuff.

Have a great weekend !
Serge
ChetanNDesaiCommented:
Record by record matching take long time when database is huge. Is there any method that work faster then this code?
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.