435,300 Members | 1,867 Online
Need help? Post your question and get tips & solutions from a community of 435,300 IT Pros & Developers. It's quick & easy.

# Stock Processing

 P: 17 Hi Student2 I am working on similar kind of thing for stock calculation but could not find any solution to my problem even after putting my problem to different forums. I saw your post that you have solved this problem of stock calculation. i need help for that too. I am using vb express and MsAccess as database. I am trying to write a query for the calculation of Stock. My tables are as under: PurchaseTable(PTId, PDate,ItemId, Description, Quantity, Price, Amount) SalesTable(STId, SDate,ItemId, Description, Quantity, Price, Amount) I want a query for stockcalculation to show data as under between some date range or at any given date: ItemId, QuantityPurchased, QuantitySold, Balance(StockQuantity+QuantityPurchased - QuantitySold) From your thread i grasped some idea that i will have to make a separate table for StockQuantity as opening stock so that it may be added to newstockpurchased. Please advise me and if you could give me the query you are using with the tables for this issue it will be highly appreciable. thanks ** Edit ** Split from Ms Access advanced calculation query, stock level? :S Sep 3 '09 #1

Ah, well.

Taking my words back. Just overlooked those zero constants in the bunch of concatenated strings.
Well, if you want to go this way, then you just need to move date checking (between start and end) to those unioned subqueries and add two more subqueries to the union to get records before start for stock opening calculation. This will certainly require to add two more zero constants.

Example of subquery before grouping:
Expand|Select|Wrap|Line Numbers
1. SELECT Quantity AS PurchasedBefore, 0 AS SoldBefore, 0 AS PurchasedWithin, 0 AS SoldWithin FROM PurchaseTable1 WHERE PDate<@START
2. UNION ALL
3. SELECT 0 AS PurchasedBefore, Quantity AS SoldBefore, 0 AS PurchasedWithin, 0 AS SoldWithin FROM SalesTable1 WHERE PDate<@START
4. UNION ALL
5. SELECT 0 AS PurchasedBefore, 0 AS SoldBefore, Quantity AS PurchasedWithin, 0 AS SoldWithin FROM PurchaseTable1 WHERE PDate Between @START and @END
6. UNION ALL
7. SELECT 0 AS PurchasedBefore, 0 AS SoldBefore, 0 AS PurchasedWithin, Quantity AS SoldWithin FROM SalesTable1 WHERE PDate Between @START and @END
8.
But, if you go this way,then why not to allow aggregating to do balance.
Expand|Select|Wrap|Line Numbers
1. SELECT Quantity AS BalanceBefore, 0 AS BalanceWithin FROM PurchaseTable WHERE PDate<@START
2. UNION ALL
3. SELECT -Quantity AS BalanceBefore, 0 AS BalanceWithin FROM SalesTable WHERE PDate<@START
4. UNION ALL
5. SELECT 0 AS BalanceBefore, Quantity AS BalanceWithin FROM PurchaseTable WHERE PDate Between @START and @END
6. UNION ALL
7. SELECT 0 AS BalanceBefore, -Quantity AS BalanceWithin FROM SalesTablel WHERE PDate Between @START and @END
8.

40 Replies

 P: 74 Dont forget to account for null values in your calculations such as: Expand|Select|Wrap|Line Numbers NZ(Qty, 0) - NZ(Sold, 0) Sep 3 '09 #2

 P: 17 Hi Stevens Thanks for your reply and guidance. Infact I need help to write a query for stock calculations as I am using Vb express and MsAccess as database. I want a query to use in Vb express which will work on the tables and retrieve the balance of stock at any given date. Please advise. Sep 3 '09 #3

 Expert 2.5K+ P: 2,653 @sazd1 What does "StockQuantity" mean at all. Something beside purchased and sold? Sep 3 '09 #4

 P: 17 StockQuantity means the OriginalStock or beginningStock or OpeningBalanceStock. I want that there should be an opening stock which will add up to the StockPurchased and subtracted from StockSold. Thanks for your reply Sep 3 '09 #5

 Expert 2.5K+ P: 2,653 Is it supposed to be one value per item (a kind of seeding) or, maybe, a single item could have multiple "StockQuantity"'s related to different date. In other words, could it be so, that "StockQuantity" relevant for a particular item at a particular date is that the latest for this item before that date? Sep 3 '09 #6

 P: 17 Hi Yes you have rightly pointed out that confusion i am having because there should be one opening balance before calculation of stock at any date: Like if i want to have stock details between 01-09-2009 to 03-09-2009 there should be an opening stock value on 31-08-2009 which will be accounted for the purchases during the period and the sales during the period . i hope i have been able to clear my point. Sep 3 '09 #7

 Expert 2.5K+ P: 2,653 But in this case opening stock value on 31-08-2009 is just purchases minus sales earlier than 31-08-2009. Having a table of stock openings you run into a problem of "same data stored in multiple places". The only reason to do it is to enhance performance while trade-off is that you will need to keep stock openings table consistent to data stored in purchases and sales tables. Are there that many records in purchases and sales tables? Sep 3 '09 #8

 Expert Mod 15k+ P: 31,494 Sazd1, You have hijacked somebody-else's thread, so I have split this into its own. Please make sure not to do so again. If you have a question then please post it in its own thread. References to other threads are permitted where necessary of course, but taking another thread over is not. Administrator. Sep 3 '09 #9

 P: 17 Hi Neopa Thanks for your correction. I never had any intention to Hijack anyone's thread. I just thought that i may extend the thread to have answer to a similar query of mine. Anyways thanks so much for your guidance. Sep 3 '09 #10

 Expert Mod 15k+ P: 31,494 @sazd1 I'm sure you did not, but I hope that you understand better now. Welcome to Bytes! Sep 3 '09 #12

 Expert 2.5K+ P: 2,653 Expand|Select|Wrap|Line Numbers SELECT  pt.ItemId, pt.Description,  SUM(pt.Quantity) AS QuantityPurchased,  SUM(st.Quantity) AS QuantitySold,  (SUM(pt.Quantity) - SUM(st.Quantity)) AS Balance  FROM PurchaseTable pt INNER JOIN SalesTable st  ON pt.ItemId=st.ItemId  WHERE pt.PDate Between @START and @END  GROUP BY pt.ItemId, pt.Description   Storing both ItemID and Description in those tables is redundant. Do you have something like Items table in your database? If so, then whether [ItemID] field in PurchaseTable and SalesTable tables is FK related to Items table? Joining SalesTable with PurchaseTable is completely wrong since it multiplies (produce all possible combinations) records with the same ItemID (join criteria) thus giving 2*2=4 records of each item which of course results in double sum. Parameters you've used have no influence since they satisfy all records returned by the join because only PurchaseTable date field is checked. PurchaseTable and SalesTable tables have to be aggregated prior to any join. At this point they should be also filtered by relevant date range. Since you need stock opening, you have to make to aggregating queries per table. One aggregating in the dates range and one aggregating before the earliest date in the range. Then you could join all the four queries on ItemID and, voila, you have dataset where each record has unique ItemID and contains all the necessary data to calculate stock balance - quantity purchase and sold before the date range and the same for the date range. Regards, Fish. Sep 3 '09 #13

 P: 17 Hi FishVal Thanks for your eloborated reply with guidance to me as a newbee to programming. My Tables are now as under: ItemsTable Expand|Select|Wrap|Line Numbers ITId    ItemId    Description  1    1    Coca Cola Normal  2    2    Coca Cola Zero PurchaseTable Expand|Select|Wrap|Line Numbers PId    PDate    ItemId    Price    Quantity    Amount  1    28/8/2009    1    1,00    10    10,00  2    28/8/2009    2    1,00    5    5,00  3    29/8/2009    1    1,00    5    5,00  4    29/8/2009    2    1,00    10    10,00  5    30/8/2009    1    1,00    10    10,00  6    30/8/2009    2    1,00    5    5,00 SalesTable Expand|Select|Wrap|Line Numbers SId    SDate    ItemId    Price    Quantity    Amount  1    30/8/2009    1    2,70    2    5,40  2    30/8/2009    2    2,70    3    7,10  3    31/8/2009    1    2,70    1    2,70  4    31/8/2009    2    2,70    2    2,70 I tried following query but it gave error "Syntax error in FROM clause" Expand|Select|Wrap|Line Numbers Dim cmdText As String = "Select a.ItemId,a.Description,Sum(QuantityPurchased),Sum(QuantitySold), " & _  "(Sum(QuantityPurchased) - Sum(QuantitySold)) as Balance " & _  "From " & _  "( SELECT pt.PDate as TransactionDate, pt.ItemId, pt.Quantity AS QuantityPurchased, " & _  "0 AS QuantitySold FROM PurchaseTable pt " & _  "UNION ALL " & _  "Select st.SDate as TransactionDate, st.ItemId, 0 AS QuantityPurchased, " & _  "st.Quantity AS QuantitySold " & _  "From SalesTable st ) a " & _  "Join ItemsTable i On a.ItemId = i.ItemId " & _  "WHERE a.TransactionDate Between @START and @END " & _  "GROUP BY a.ItemId, i.Description " Please correct this query what I am doing wrong with this. And also I will request you to please modify this query to accomodate the OpeningBalance issue. Because the programme has to display StockReport between some date range and definitely it will have to account for the balance available for the items immediate prior to the date range as very rightly advised by you. Thanks for all your help and guidance. Sep 4 '09 #14

 Expert Mod 15k+ P: 31,494 You seem to be keeping me busy Sazd1. Please read the notes by your posts where I've had to edit them. Posting code without tags is not allowed. It also makes your posts harder to read, and therefore less likely that people will want to help you. Sep 4 '09 #15

 P: 17 Hi NeoPa Please accept my apologies again. I will try not to repeat such mistakes in future and I realize that being an expret you have to tackle so many important issues. Thanks again for your patience to me. Sep 4 '09 #16

 Expert Mod 15k+ P: 31,494 Your polite attitude makes it much easier. I hope you find all the help you need :) Sep 4 '09 #17

 Expert 2.5K+ P: 2,653 @sazd1 The best way to correct it is totally drop it down. As I've already said you should perform aggregate query on your tables (to get datasets where each record has distinct ItemID) prior to join. If you have difficulties with subquery syntax, then try to create sequence of queries in you access backend. Kind regards, Fish. Sep 4 '09 #18

 Expert Mod 15k+ P: 31,494 @FishVal Subqueries in SQL (in case it helps). Sep 5 '09 #19

 P: 17 Hi NeoPa Thanks. Yes i think the topic of Subqueries will definitely help to understand how subqueries work. I will go through it and will let you know of my problems again. Sep 5 '09 #20

 Expert Mod 15k+ P: 31,494 Clearly, you can define QueryDefs and use them as sources in your queries, but it's also possible, using SQL, to include a subquery within your QueryDef. This is explained in more detail in the linked article. Which approach you proceed with is then down to what you feel most comfortable with. Sep 5 '09 #21

 P: 9 On the subject of Stock Tracking and Processing, I recently implemented fairly sleek logic that can run off of a SQL Query using Purchase and sales from a single table and also give me the Opening and Closing Stock as of any date, which is quite helpful. The logic can also give you the Opening and Closing stock value on waited average method of Inventory. I was wondering if anyone can help find the value of Goods based on FIFO (first in first out method) here is what I am doing: Table Structure: TxnDate StockCode Action (Purchase or Sale) Qty Rate Lot Quantity on Hand = (On every Sale reduce the LotQOH, to track FIFO) Opening Stock = SUM(PurchaseQty-SaleQty) Where TxnDate < StartDate Purchase = Sum(Purchase) where TxnDate Between StartDate and EndDate Sale = Sum(Sales) where TxnDate Between StartDate and EndDate Closing Stock = SUM(Purchase-Sale) Where TxnDate < EndDate On the same lines, Value of Closing Stock = Total Purchase Cost - Total Cost of Goods Sold which in SQL terms is Value of Closing Stock = Sum(Purchase.Qty*Rate) as TPurchaseCost, TPurchaseCost / Sum(Purchase.Qty)*Sum(Sale.Qty) --This is = Average Cost of Goods on Hand.. The question is HOW To determine FIFO Cost Sep 11 '09 #22

 P: 9 @sazd1 These links don't work.. Am I missing something ? Sep 11 '09 #24

 P: 17 ** Edit - Links removed ** Ok try the above site link and there search Fifo Stock developed by Khawar Sep 11 '09 #25

 P: 9 @sazd1 Looks sensible.. How may I help ? Sep 11 '09 #26

 P: 9 While the solution demonstrates a method of recording FIFO, it is limited to only show the CURRENT FIFO based stock valuation. The tables cannot show the FIFO stock balance and Stock FIFO value as of a certain date, which is what I am interested in. I've already posted the solution to calculate Opening and Closing date as of any date. The first thing and decent inventory management will offer is Stock Position as of a Date, which is not necessarily current. Sep 11 '09 #27

 Expert 100+ P: 1,287 Sorry, but I'm not familiar with FIFO stock tracking. Can you explain how these values that you want are calculated in general? Then we will know whether your data structure supports the calculations. Sep 11 '09 #28

 P: 9 @ChipR Due to limited time, I could only point you to an explanation. http://accountinginfo.com/study/inve...entory-120.htm Bottom line - I need to display the Cost of Goods on Hand as of any day. Sep 11 '09 #29

 Expert 100+ P: 1,287 Thanks for the link, that explained very nicely. When you say goods on hand, isn't that the same as ending inventory as of a certain date? Cost of ending inventory = Beginning inventory + Cost of purchases - Cost of goods sold Sep 11 '09 #30

 P: 9 @ChipR yes that's correct.. Sep 11 '09 #31

 Expert 100+ P: 1,287 Okay, let me know if I'm off track on any of this. Beginning inventory - you've figured that out already Cost of purchases - Sum(Qty*Rate) where action = purchase Cost of goods sold - Sum(Qty*Rate) where action = sale These are constrained by date, of course. The only information I can see that you may be missing is the Rate on sales, but shouldn't that be filled in at the time of sale? Sep 11 '09 #32

 P: 17 Hi Justwandering I am trying to add OpeningBalance column in my query. But I am having problem with the WHERE a.TransactionDate < @START Please advise where i can put this, because Where clause is used after From clause and i have to use two WHERE clauses one for OPENINGBALANCE and the other for the stock position between two dates. Please go through my query i have highlighted the wrong WHERE clause portion, and advise how to place it in correct position. Thanks Expand|Select|Wrap|Line Numbers Dim cmdText As String = "Select a.ItemId,a.Description, " & _         "(Sum(QuantityPurchased) - Sum(QuantitySold)) As OpeningBalance WHERE a.TransactionDate < @START, " & _         "Sum(QuantityPurchased) AS QuantityPurchased, " & _         "Sum(QuantitySold) AS QuantitySold, " & _         "(Sum(QuantityPurchased) - Sum(QuantitySold)) AS Balance " & _         "From " & _         "(" & _         "SELECT pt.PDate as TransactionDate, pt.ItemId, pt.Description, pt.Quantity AS QuantityPurchased, " & _         "0 AS QuantitySold FROM PurchaseTable1 pt " & _         "UNION ALL " & _         "Select st.SDate as TransactionDate, st.ItemId, st.Description, 0 AS QuantityPurchased, " & _         "st.Quantity AS QuantitySold From SalesTable1 st " & _         ") a " & _         "WHERE a.TransactionDate Between @START and @END " & _         "GROUP BY a.ItemId, a.Description" Sep 12 '09 #33

 P: 9 Friends, I am a bit occupied for the next 2 days, but will come back. Sep 12 '09 #34

 Expert 2.5K+ P: 2,653 @sazd1 There is nothing to correct in your query since it is not correct in all points. If you want to make any calculation in query, then you need to obtain such dataset that calculation arguments appear in the same record. This could be done by first aggregating purchases and sales tables by [ItemID] and then joining thus obtained datasets by equal [ItemID]. On the other hand you may union purchases and sales tables but quantity field in sales table has to be negated to make summing correct. P.S. It will be better if you break down logic of your task into several sequential steps thus going from one stage to another when one has been completed. Here is an example of what it could be like: Query returning purchases sum per item till particular date. Query returning sales sum per item till particular date. Join the above two queries to get dataset where each record contains unique itemid, sales sum for this particular item till particular date, purchases sum for this particular item till particular date. Add callculated field to the above query which subtract purchases sum from sales sum. Thus you get dataset which is list of itemid's with correspondent stock openings for the particular date. Congratulate yourself and go further. ?????? PROFIT !!! Sep 12 '09 #35

 Expert 2.5K+ P: 2,653 Ah, well. Taking my words back. Just overlooked those zero constants in the bunch of concatenated strings. Well, if you want to go this way, then you just need to move date checking (between start and end) to those unioned subqueries and add two more subqueries to the union to get records before start for stock opening calculation. This will certainly require to add two more zero constants. Example of subquery before grouping: Expand|Select|Wrap|Line Numbers SELECT Quantity AS PurchasedBefore, 0 AS SoldBefore, 0 AS PurchasedWithin, 0 AS SoldWithin FROM PurchaseTable1 WHERE PDate<@START UNION ALL SELECT 0 AS PurchasedBefore, Quantity AS SoldBefore, 0 AS PurchasedWithin, 0 AS SoldWithin FROM SalesTable1 WHERE PDate<@START UNION ALL SELECT 0 AS PurchasedBefore, 0 AS SoldBefore, Quantity AS PurchasedWithin, 0 AS SoldWithin FROM PurchaseTable1 WHERE PDate Between @START and @END UNION ALL SELECT 0 AS PurchasedBefore, 0 AS SoldBefore, 0 AS PurchasedWithin, Quantity AS SoldWithin FROM SalesTable1 WHERE PDate Between @START and @END   But, if you go this way,then why not to allow aggregating to do balance. Expand|Select|Wrap|Line Numbers SELECT Quantity AS BalanceBefore, 0 AS BalanceWithin FROM PurchaseTable WHERE PDate<@START UNION ALL SELECT -Quantity AS BalanceBefore, 0 AS BalanceWithin FROM SalesTable WHERE PDate<@START UNION ALL SELECT 0 AS BalanceBefore, Quantity AS BalanceWithin FROM PurchaseTable WHERE PDate Between @START and @END UNION ALL SELECT 0 AS BalanceBefore, -Quantity AS BalanceWithin FROM SalesTablel WHERE PDate Between @START and @END   Sep 12 '09 #37

 P: 17 Hi Fishval Thanks for your reply. I will try to implement this and will let you know the results. Thanks again. Sep 14 '09 #38

 P: 17 Hi Neopa, Fishval & Justwandering. Thanks to all for giving me suggestions and guidance to solve this issue which was on my head for last couple of months. I specially want to thank Fishval for guiding me with a query due to which i finally was able to get those so desired results of Stock calculations. The following query produced the long waited results for me.Any further improvement or alternate of this query will be highly appreciated. Thanks once again. Expand|Select|Wrap|Line Numbers Dim cmdText As String = "Select a.ItemId,a.Description, SUM(PQuantityBefore) AS PQuantityBefore, " & _         "SUM(SQuantityBefore) AS SQuantityBefore, " & _         "(Sum(PQuantityBefore) - Sum(SQuantityBefore)) AS BalanceBefore, " & _         "Sum(QuantityPurchased) AS QuantityPurchased, Sum(QuantitySold) AS QuantitySold, " & _         "(Sum(PQuantityBefore) - Sum(SQuantityBefore) + Sum(QuantityPurchased) - Sum(QuantitySold)) AS Balance " & _         "From " & _         "(" & _         "SELECT pt.ItemId, pt.Description, pt.Quantity AS PQuantityBefore, " & _         "0 AS SQuantityBefore, 0 AS QuantityPurchased,0 AS QuantitySold FROM PurchaseTable1 pt " & _         "WHERE pt.PDate < @START " & _         "UNION ALL " & _         "SELECT st.ItemId, st.Description, 0 AS PQuantityBefore, st.Quantity AS SQuantityBefore, " & _         "0 AS QuantityPurchased,0 AS QuantitySold FROM SalesTable1 st " & _         "WHERE st.SDate < @START " & _         "UNION ALL " & _         "SELECT pt.ItemId, pt.Description, 0 AS PQuantityBefore,0 AS SQuantityBefore, " & _         "pt.Quantity AS QuantityPurchased, 0 AS QuantitySold FROM PurchaseTable1 pt " & _         "WHERE pt.PDate Between @START and @END " & _         "UNION ALL " & _         "SELECT st.ItemId, st.Description, 0 AS PQuantityBefore,0 AS SQuantityBefore, " & _         "0 AS QuantityPurchased, st.Quantity AS QuantitySold FROM SalesTable1 st " & _         "WHERE st.SDate Between @START and @END " & _         ") a " & _         "GROUP BY a.ItemId, a.Description"   Sep 16 '09 #39

 Expert 2.5K+ P: 2,653 You are quite welcome. Sep 16 '09 #40

 P: 9 @ChipR Oh well.. It took a while to get here, but I glad that I got the logic now.. based on earlier theories, here's my updated thought: 1. Value of Opening Stock = Value of Total Purchases - (Value of Sales - Profit or Loss), where Date < StartDate 2. Quantity of Opening Stock = Total Quantity Purchased - Total Qty Sold, , where Date < StartDate 3. Purchase Value = Sum of Purchase in amount where date between start and end date. 4. Purchase Qty = Sum of Purchase in Qty where date between start and end date. 5. Sale Value = Sum of Sales in Value where date between start and end date. 6. Sale Qty = Sum of Sales in Qty where date between start and end date. 7. Closing Stock Qty = Sum Purchase Qty - Sum of Sales in Qty where <= end date. 8. Closing Stock Value = Sum of Purchase value - (Total Value of Sales - total Profit or Loss), where Date <= EndDate Just to add a last point about Cost of Sales : I am calculating P/L for each txn, on a FIFO basis. Having followed the table structure, I proposed earlier, I am now able to calculate stock on both Average and FIFO method. THE ICING on the cake is the single sleek query that does the job quite efficiently. Hope this serves as a good reference point for those in need. Sep 17 '09 #41