# 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
Sell                            4                       ?                                300
Sell                            4                       ?                                500

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

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

Accountant/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
Commented:
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

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 @prevSum   int
declare @ssum      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
select id, remaining_units, pprice from Trans where security = @security and remaining_units > 0 order by id

set @ssum = 0
set @prevsum = 0
set @done = 0
While @@FETCH_NEXT = 0 and @done = 0 BEGIN
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
END

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.

Commented:
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.
Commented:
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 @prevSum   int
declare @ssum      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
select id, remaining_units, pprice from Trans where security = @security and remaining_units > 0 order by id

set @ssum = 0
set @prevsum = 0
set @done = 0
While @@FETCH_STATUS = 0 and @done = 0 BEGIN
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
END

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.
Commented:
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 @prevSum   int
declare @ssum      int
declare @SELLid    int
declare @remainingunits int
declare @GLTotal float
declare @done      int
declare @msg         varchar(255)

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
select id, units, remaining_units, pprice
from Trans
where security = @security and remaining_units > 0
order by id

set @ssum = 0
set @prevsum = 0
set @done = 0
set @GLTotal = 0
While @@FETCH_STATUS = 0 and @done = 0 BEGIN
set @GLTotal = @GLTotal + (@remainingunits * @pprice)
update Trans set remaining_units = 0 where id = @BUYid
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
END

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

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.
Author 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
Commented:
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.

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

Author 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
Commented:
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 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 = 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

prevsum = 0
done = False
GLTotal = 0

remainingunits = rs("remaining_units")
pprice = rs("pprice")

GLTotal = GLTotal + (remainingunits * pprice)

db.Execute "Update Trans set remaining_units = 0 where id = " & CStr(buyID)
done = True
db.Execute "update Trans set GL = (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.
Commented:
ugh, it wrapped!
Commented:
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.
Author 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
Commented:

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

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

Author 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

Commented:
Hi Serge,

It is on the way.

bbfn,
Paul.
Author 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
Author 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
Commented:
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     Sell           2            \$15
A1     Sell           4             \$10
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.

bbfn,
Paul.

P.S. A month vacation?  Sounds like fun!  I haven't had a vacation in 7 years longer than 36 hours.
Author 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
Commented:
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.