A Classic Inventory Tracking Problem

[Dwight]

Hello,

I have an inventory tracking problem that is tailor made to be solved by a database. I just need a little help getting it together. A sample database with a table is attached to help illustrate the situation.

Basically, inventory is purchased in large blocks and sold later in smaller blocks. I need to track the cost on a First in First Out (FIFO) methodology.


For example:


Jan 1 - 1,000 units of product XYZ are purchased at $10.

Jan 15 - 500 units of product XYZ are purchased at $8.

Jan 20 - 800 units of product XYZ are sold at $15.

Jan 25 - 400 units of product XYZ are sold at $9.


My solution is to have Access create a LotNumberID for the purchases and then allocate the sells to these lots.

The lots needed in this example would be:

Jan 1 Purchase = Create Lot 1

Jan 15 Purchase = Create Lot 2

Jan 20 Sale = Allocate 800 units to Lot 1.

Jan 25 Sale = Allocate 200 units to Lot 1 and Allocate 200 units to Lot 2.

My problem is that I do not know how to get Access to handle these LotNumberID's properly. With Jon K's help I have mastered running sums in Access but this calls for something different. You can not use the Select(*) Count function because then it will give you Lot 1, Lot 2, Lot 3, Lot 4.

I think databases were built to solve problems like this so I know it is doable. I just need help with the syntax.

Thanks in advance - I am always suprised at the quality of the responses the users of this forum provide.

Dwight.

 

[Dwight]

I made a small mistake in the sample database. The Sells should be negatives in the Table. Sorry for my oversight if anyone has spent time on this.

 

[Dwight]

I have continued to work on this and am pretty much stuck where I thought I would get stuck.

I have updated the sample database with my current state of progress. Basically, it counts the buy lots, counts the sell lots and then puts them together in a Query called LotBalance. The first calculation is correct but after that they are wrong because I can not get it to do the allocation procedure I talked about in the original post. A quick look will make it clearer than more rambling from me.

Maybe this approach is alltogether wrong. Let me know and I will scrap it. I do not know VB so I would like to do this with queries.

Thanks,

 

[Dwight]

I have a solution....but only in theory. Hopefully someone on this board has done something similar and can help me out.

If the data in the transactionhistory table was recorded differently then writing the queries to do what I want would be a snap.

A buy will always represent a new lot number. For example a buy on Jan 1 for 1,000 would be Lot1 and a buy for 500 on Jan 15 would be Lot2. But the sell lots have to match up with the buy lots by tracking their balance. A Sell of 1,000 on Jan 2 would be Lot1 and a sell of 500 on Jan 16 would be Lot2. This the easy solution but does not match what happens in the real world.

If there was a sell of 800 on Jan 2 this would be Lot1 and then if there was a sell of 500 on Jan 16 part of it would be Lot1 (200)and part would be Lot2 (300). This is what I explained before.

Would it be possible to have the user enter the transactions in "bulk" but have the database record them in multiple records i.e. can it track the balance and break out the sells into their respective lots?

How would I do this? With Update/Append queries to my table?

Sorry for the length and thanks for the help.

 

[Dwight]

I posted this a while back and it is still an outstanding item on my to do list.

If anyone has experience tracking inventory cost with Access I would appreciate your insights into this matter.

 

[Dwight]

I hope I am not annoying anyone by continually drudging up this post. If Jon K, dcx, or Pat Hartman could have a look I would appreciate it.

Here is a database with my current state of progress. It does not work but I thought (hope) it might spark an idea in someone else's mind.

My current problem is that I cannot deal the the partial allocations. For example in the attached database only the first lot is correct.

Forget about my other posts. I think I was on the right trail with the first one.

Thanks and sorry to bring this up again.

Dwight

 

[simongallop]

Before getting too involved a few questions (as ever!).

Is this db being built to track the physical lots if so are you going to be keeping the serial numbers of the lots etc?

What output are you looking for and how is the database going to be controlled ie by forms or data straight into the tables?

Irrespective of what you do, would suggest that you split it into 3 tables.

Table1: Bought: Id_Num, Product_ID, Date, Size, PricePerUnit
Table2: Sold: ID_Num, Product_ID, Client_ID, Date, Size, PricePerUnit
Table3: Allocation: Alloc_ID, Sold_ID, Bought_ID, Lots

Table3 will be showing the break down of the sales ie using your first post as the example it would look like:

Alloc_ID: 1
Sold_ID: 1
Bought_ID: 1
Lots: 800

Alloc_ID: 2
Sold_ID: 2
Bought_ID: 1
Lots: 200

Alloc_ID: 3
Sold_ID: 2
Bought_ID: 2
Lots: 200

This will allow you to run queries showing what stock you have left how much profit you made per transaction etc as the data is held in an easy to manipulate format.

If you need help in filling table 3 shout (once you have created the tables!) Would suggest using code to do this.

HTH

 

[FoFa]

Harry is correct in assigning a third table to track the allocations. This way you are not limited on how many allocation can be placed against a purchase. For example:

Purchase 100
Purchase 100
Purchase 100

Sell 250

Now you have one sell against 3 Purchases. Usually you need to use code to track the allocation and split them correctly (queries are nice, but sometimes it takes VBA). The code is not all that complicated, simple math and inserts into that third table. Once you have that all the links are in place to do what ever you want.

 

[Dwight]

Thanks for the direction. The only programming I've had was a little PASCAL in 8th grade so I've got some work to do on this VBA. I plan on ordering:

"The Waite Group's Visual Basic Database How To, The Definitive Problem Solver" to get me up to speed.

Let me digest it over the next couple of weeks. This thread my get a little longer but I need to learn first. I'm happy to give up the query route though. Nothing but dead ends.

Kind regards,

Dwight

 

[Pat Hartman]

Here are some book recommendations since you're going into study mode:

Access Database Design & Programming(3rd edition) by Steven Roman, O'Reilly & Associates, Inc, $34.95. This book covers normalized table design, Access SQL and programming with ADO.

Access Cookbook by Ken Getz, Paul Litwin & Andy Baron, O’Reilly & Associates, Inc., $49.95 with CD. Solutions to practical user interface and programming problems. - This is my favorite VBA book. It is not a language reference in that it does not lay out all the elements of VBA but it shows you practical uses of code.

A Visual Introduction to SQL (2nd edition) by David Chappell & J. Harvey Trimble Jr., John Wiley & Sons, Inc. $44.99

Troubleshooting Access Databases by Virginia Andersen. Microsoft $19.99. Solves many of the problems you’ll run into.

Microsoft Jet Database Engine Programmer’s Guide by Dan Haught and Jim Ferguson, Microsoft Press $39.95 w/CD. Be sure to get the latest version. This book is updated for each new version of Jet. Covers the complete object hierarchy, working with collections, security, optimization, and how Jet interfaces with the ODBC drivers. I actually just bought a new copy of this book. The latest edition seems to be for Jet 3.5. The book is out of print but I found a new copy for only $5 by searching the Borders site.

You have a many-to-many relationship so the table structure suggested by Harry is the way to go. I'm sorry I don't have any specific code to offer but you will need to do this with VBA. Queries do not support the type of logic you need.

 

[Dwight]

Harry,

I have been studying the solution you provided to my problem. I follow and appreciate its logic (I still need help implementing it) but breaking my single table into 3 parts would require a major overhaul of my database. Therefore, I would like to create an allocation table, as recommended, but instead of splitting the Transaction History table into a Buys table and a Sells table would it be possible to create two queries that simply filter the Transaction History table into the buys and sells? Could the VBA look at these and then make the appropriate insertions into the Allocation Table?

Fofa's response seems to imply that I only need a third table to track the allocations but Pat Hartmen's says I need 3 tables.

I will give you the whole background and then present the problems breaking up the table would cause. I have been wrestling with these issues and have solved some of them but the whole mess could be avoided if I don’t have to break up the table.

Background

When originally posting I tried to put boil my problem down into its simplest elements and use terminology that the most people could identify with. I thought a lot of people used databases to track inventories so I created a proxy problem in these terms. I hoped once I got the concepts I could adapt it back to my actual application but this turned out to be a bad move. I should have just thrown my original problem out there. I did not want to get greedy with the free advice.

My database is used to track investment portfolios i.e. stocks and bonds. This particular feature will be used for tax reporting purposes. Here are some major components of the database structure:

Transaction History Table – This holds all the buys, sells, deposits, withdraws, dividend payments, interest payments, and fees for each client’s portfolio. Every investment has a unique identifier that allows it to be tracked.

Potential Problem: I use a crosstab query to sum up the Net positions on this table. Buys are positives and sells are negatives so I add them less than an entered date and I get the net position.
Potential Solution: Create a Union query to recombine and then perform a crosstab on that? I just discovered Union queries but I think it would work.

Transaction Entry Form – A data entry form to enter information into the Transaction History table. It is convenient to have 1 form for the user to enter all the different transactions. I have combo lists to make the selections.

Potential Problem: How would the data be assigned to the correct table if there were multiple tables? For example, the user enters a buy for the client Jones, then a sell, and then a fee. How would this get split to different control sources?
Potential Solution: I tried using IIF statements on the control source but this did not work. For example, IIF ([TransactionID] =”Sell”, [Sells],[Buys]).

Reconciliation Form – Every month the database must be reconciled with a bank statement. This displays all the transactions from the Transaction History table in datasheet view and lets the user verify them or make required changes.

Potential Problem: The data would have to be pulled together but then when the user makes a change it would have to be corrected in the correct underlying table. I don’t know how to do this.

Potential Solution: Don’t split the table……….

These are the reasons I don’t want to split the table. I have achieved a lot by keeping everything simple and would like to continue this way.

So just to get back to the main point: Is it possible to create an Allocation table, 1 Transaction History Table, and then have 1 Buy query and 1 Sell Query for the VBA to evaluate in order to make the necessary insertions into the Allocation table?

A sample database is attached. I need help with the code. The only code I know comes in handy at the automated cash machine.

Kind regards,

Dwight

 

[Pat Hartman]

Having purchases and sales in the same table is fine. In that case you would need only two tables. The allocation table would relate 2 records from the same table rather than records from 2 different tables as it would in the three table scenerio.

I don't think that your method of calculating a net position will work here. Don't you need a net position by lot? You need to know how many shares of each lot remain rather than the total number of shares. When you choose shares to sell you can choose them by purchase price or purchase date (or a combination of both) depending on what type of tax impact is desired.

tblAllocation
AllocationID (autonumber primary key)
PurchaseLotID (foreign key to a purchase transaction)
SaleLotID (foreign key to a sale transaction)
SharesSold

To find the available lots you need two queries. The first sums the Allocation transactions and the second joins that query back to the transaction table to obtain the lots that have not been completely sold.

query1:
Select A.LotID, Sum(A.SharesSold) As TotalSold
From tblAllocation as A
Group By A.LotID;

query2:
Select T.Cusip, T.LotID, T.Amt - Nz(Q.TotalSold, 0) As AmtAvailable, T.PurchaseDate, T.PurchasePrice
From tblTran as T Left Join query1 as Q ON T.LotID = Q.LotID;

query2 will give you the quantity presently held.

We need to zero in more on your requirements before any code should be written.

Your inventory example was fine although managing inventory that needs to match specific items sold to specific items purchased is not very common. You would only see it with items that have expiration dates such as photographic film or items that are "big ticket" and have serial numbers.

 

[Dwight]

Pat you always seem able to understand the issues of a problem better than the person grappling with it. I suspect that comes with experience so hopefully I can someday catch up.

For tax reporting purposes the IRS allows 2 methods:

1) Specific Identification – “If you can adequately identify the shares of stock or the bonds you sold, their basis is the cost or other basis of the particular shares of stock or bonds”

2) First In First Out (FIFO) – “If you buy and sell securities at various times in varying quantities and cannot adequately identify the shares you sell, the basis of the securities you sell is the basis of the securities you acquired first”

My firm is looking for a consistent methodology that can be applied uniformly across the client base and therefore we will use the FIFO approach. We are not tax specialists and do no tax reporting. Clients are advised to seek experts in this highly complex field. In addition we may inherit accounts with complicated histories that we do not want to untangle.

In response to your question, we need to know the total position and the amount remaining for each lot. Since the lot balances are deducted chronologically it should be possible for the database to track this automatically, i.e. when 1 buy lot is used up by a sell the excess will spill over into the next buy lot.

Below are my goals:

Calculate:
1) Total Position – The total units held for a particular security on a specified date.

2) Realized Gains/Losses – The realized gains or losses from historical transactions. This is calculated by matching the buys and sells based on FIFO. All information is contained in the Transaction History table. It just has to be put in the Allocation table correctly. Going back to FoFa’s post, although there is only 1 sell there would be 3 different realized gains because there were 3 buys, each at a different price.

3) * Unrealized Gains/Losses - Here you compare the average cost of the accumulated position and compare it to the current security price. The cost is the weighted average of the buys multiplied by the purchase price. This tells you what the potential realized gains/losses are.

There is one complicating factor that has prevented me from achieving this. The problem arises if a client takes a position in a security, sells the position, and then repurchases the security at a later date. The first purchase price and the second purchase price cannot be commingled. The calculation should “reset” if the total position ever gets to 0 and then becomes positive again. I’ve tried queries but without luck. I suspect VBA could bang this out.

To reiterate number 3 is just the average cost. I put a star by it because I have not discussed it before.

All this will be the basis of two reports. The position summary report will have the current outstanding position, the average cost of the position, and the unrealized gain/loss.

Then there will be a realized gain/loss report. It will show the lots and the gain or loss of each.

I’m not looking for someone to sit down and knock it all out. I’m just trying to paint a clear picture so nobody wastes their efforts. I realize everything has to be understood concretely for coding to be effective. Bombard me with questions if it will help. I could have some logic holes.

As I stated in my first post, I’m consistently surprised at the quality of responses in this forum.

Kind regards, Dwight

 

[Pat Hartman]

I must confess to spending 5 years managing the portfolio managment systems for Wright Invesors' Service. But, that was over 20 years ago so details are fuzzy.

1. You can do this by summing all purchases and sales prior to a given date.
2. I still think that this needs to be done by lot. If you take on an existing account and the records do not include enough details to store the holdings by lot, can't you just lump everything into a single lot. The end result of the processing would be this anyway. But forcing this on converted accounts lets you do the "correct" thing from that point forward.
3. This is also pretty simple if you do it by lot. The queries I posted should help you with this. You will need to modify them so that they choose records prior to a given date. If you do the calculation by lot, you won't have to worry about the customer's position going to 0 at some point in time. You will NOT be averaging the unrealized gains/losses, you will be calulating the actual gain/loss if the security is sold at the current price.

I personally don't like your company's decision to go with FIFO only. Even though you don't offer tax advice, the customer should be able to direct the sale to achieve his tax goals. Our system at WIS didn't have any trouble with this even though it was written in COBOL and FORTRAN and ran on an antiquated IBM/360.

 

[Dwight]

Pat,

I’m sure Wright’s systems have never run as well.

I agree with you. Originally I lobbied for the specific identification method. I was told FIFO was sufficient (basically because this is how it has always been done). However, as it is more informative, better for the client, and most importantly doable, it would be just shy of stupid not to go the specific identification route. Plus it was my original idea. Thanks for the push.

I have some embarrassingly simple questions:
I could not get your queries to work. I set up the allocation table as recommended (I added client, cusip, and date fields). I pasted in your text but got syntax errors. I checked it with a reference book of mine and it looks good so I must be doing something incorrect. (Hard to imagine!) I get the error:
You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field. (Error 3306)
I got the same error for Query 2 but I think it is a carryover from Query 1 since they are linked.

Do you have any recommendations on the most efficient, attractive, and intuitive way for the account manager to fill out the Allocation table?

The idea I had was a form/subform combination. The results of Query 2 would be the information in the subform and then the user could assign the lots in the top part. I created a test form with this methodology.

Attached is a sample database. I don’t think I have things structured exactly as they should be…..but they feel close. If you could have a look at the queries, table structure, and form idea I would be indebted. Once I get a small working model I feel confident I can study it and expand it i.e. go from bullet point 2 to bullet point 3 on my own.

This is a big project for me but I know everything I want to do is possible…..at least as long as I stay in this board’s good books.

Kind regards, Dwight

 

[Pat Hartman]

I redid your tables but I really can't write the code you need. You can add a button to the form I built and in that click event run the code to allocate the sale. Give it a shot and post back. I had to convert your db to XP to work on it and then I converted it back. Hopefully you won't have any problems with the changed version.

I'm not sure why you were using replication GUID's as your primary keys but I changed them to normal autonumbers. If you really need GUID's you can change them back.

 

[Dwight]

Above and beyond the call of duty Pat!

I will digest this over the next couple of days and then see where I stand. I'm going to Copenhagen for two days. My girlfriend won't let me take the laptop but I'll pack the Access-VBA book for sure.

Thanks again,

Dwight

 

[Pat Hartman]

Copenhagen, is a beautiful city. Have fun

 

[anchorp]

How did it turn out?

Did you or anyone else figure this out? I am facing the same problem.

Thanks!

 

[Dwight]

This was a long time ago.....I do remember that I was able to use the advice/solution provided to accomplish what I needed.

In the end, we did not end up using the functionality. Give it a go and if you need help maybe I can help.