Results 1 to 8 of 8
  1. #1
    Join Date
    May 2007
    Location
    Kampala
    Posts
    3

    Question Unanswered: Getting inventory levels in access data base

    I must state here first that I am very new to this programming staff. I am trying to developie a small grocery shop inventory-tracking database. I am able to calculate for the closing stock but I am not able to transform that into the opening stock of the next day. Please help on how I can achieve this in either a query or report.

    Please use the simplest method i can use.

    Thanks

    Kamusteve

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    unless you have shrinkage overnight then by defintion if you can clacualte the closing stock at night, then re-running the same calculation should give the opening stock
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    As healdem pointed out unless you have stock "disappearing" over night you should have the same figure as the closing stock calculated. However, you need to be aware of other issues. You may (will) have stock passed its sell by date, damaged or unfit to sell. Is this taken in to consideration?? It WILL make your opening stock differ to the closing stock figure.
    Im also guessing that you may not have coded the opening stock - closing stock
    ie
    Opening_stock = Opening_stock - closing_stock
    If you are relying on typing in to a record whats left so that it alsway maintains a record you may need to re-think

    may be a discription or example pt on here could afford you somehelp?

    gareth

  4. #4
    Join Date
    May 2007
    Location
    Kampala
    Posts
    3

    Exclamation

    Thanks healdem and gareth. But may be i did not put clear my problem. I do have a query of Transactions. In the query i have TransID, TransDate, ProductName, ProdcutCategory, PurchaseQty, UnitCost PurchaseValue, SaleQty, UnitSalePrice, SalesAmount, ClosingStock:[PurchaseQty]-[saleQty].

    I want now to move on so that the other query i have can automatically give me closing stock as opening stock of the previous day. With this, i shoul be able to display previous stock level so as to be able to Continuously and automatically get opening and closing stock of the day.

  5. #5
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Quote Originally Posted by Kamusteve
    Thanks healdem and gareth. But may be i did not put clear my problem. I do have a query of Transactions. In the query i have TransID, TransDate, ProductName, ProdcutCategory, PurchaseQty, UnitCost PurchaseValue, SaleQty, UnitSalePrice, SalesAmount, ClosingStock:[PurchaseQty]-[saleQty].

    I want now to move on so that the other query i have can automatically give me closing stock as opening stock of the previous day. With this, i shoul be able to display previous stock level so as to be able to Continuously and automatically get opening and closing stock of the day.
    OK as a thought (feeling that you may on a steep learning cure). Why not have a command button running a macro which SETVALUE from a closing stock figure to the new opening stock. This could be activated by an AFTERUPDATE command brought about by placing the next trading days date in the record set. That way it will maintain (if you want it to ) the previous dates information and then set up the next trading days (by date) opening stock you just put in the closing stock and away to go same process again

    Gareth

  6. #6
    Join Date
    May 2007
    Location
    Kampala
    Posts
    3

    Thanks

    Thanks gareth

    Where as i am about to be clear with what i want, there are still some problems. Please would you highlight for by example, exactly how i am supposed to achieve this. I will go a head and have some tutorials on macros. But as of now i wish you could post for me the procedures i should go through to have closing as opening records.

    Thanks again

    kamusteve

  7. #7
    Join Date
    Sep 2006
    Posts
    265
    I don't quite understand the PurchaseQty etc and SalesQty etc on a Transactional file but ...

    assuming that to get Opening and Closing Stock is a function of going through Transactions adding Purchases values and subtracting the Sales values. Personally I would approach the Stock as flagging the Transaction with a type P and S and depending on that Type: Add or Subtract.

    I would hold the Stock Valuations in a separate file even in the Products Table if the Opening and Closing positions do not need to be retained.

    OR

    Hold the Opening and Closing Values in a separate if they need to be retained with possibly an Closing Date and Opening Date (Close Date + 1). Run an update on additions and sales creating the Balance of Stock.

    Whilst this is not normalised behaviour I can see the requirement to get to the stock balances. In your query to get the balances link this Balance Table via the Product and TransDate or something along those lines.

    This is just suggestions as you know best how your system needs to work.

    Simon

  8. #8
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    May I suggest as a way of speeding up this resolution that you might post the db on here with dummy data on it so we can visualise what your doing and perhaps get you to where you want to be quicker?

    Gareth

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •