none
How to make a Daily Inventory Report RRS feed

  • Question

  • Is it possible to make a report of daily inventory record of each item for each day.(or even just if there is a transaction for that date for the specific item). if possible, how?

    For example:

    3/5/2018-product1-50pcs

    3/6/2018-product1-350pcs

    3/7/2018-product1-350pcs

    3/8/2018-product1-330pcs

    Tuesday, March 20, 2018 1:40 AM

All replies

  • Hi,

    It should be possible but how would depend on your table structure. Can you show us your table structure? Thanks.

    Tuesday, March 20, 2018 1:43 AM
  • It would be easy to get a count of products at a specific time/date and show that count, but if you want to know past counts with the current count you would have to also save that count somewhere to pull it up with the current dates report. That or you have to have an individual record for each product with a date that it was added.

    Not knowing anything more about your structure, I would recommend a simple solution like the following. Create a table to store the Date/Product/Count. Then you can have a button that produces the report, but as it's first step it could add a record for the current date (if it does not exist, or replace a record for today as needed) to the same table. That way when the report is produced it will have all the data you need. If you don't need the history "forever" you could have it delete records older than X days to dump the oldest data.

    A lot more can be done with something like this, but maybe it'll give you an idea based on your actual requirements and existing structure.


    Larry

    Tuesday, March 20, 2018 2:37 AM
  • You'll find an example of a simple inventory database as Inventory.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file, to do waht you want, you'd first have to add an auxiliary calendar table to the database, which is simply a table of all dates over a range, e.g. 10 years, with a single column calDate.  Then you'd need to amend the qryStockMovements query as follows:

    SELECT AcquisitionDate,"Acquisition" AS MovementType, ProductID, Quantity
    FROM StockAcquisitions
    WHERE AcquisitionDate >=
        NZ((SELECT MAX(StockTakeDate)
         FROM StockTakes
         WHERE StockTakes.ProductID = StockAcquisitions.ProductID))
    UNION ALL
    SELECT DisposalDate,"Disposal", ProductID, Quantity*-1
    FROM StockDisposals
    WHERE DisposalDate >=
        NZ((SELECT MAX(StockTakeDate)
         FROM StockTakes
         WHERE StockTakes.ProductID = StockDisposals.ProductID))
    UNION ALL
    SELECT StockTakeDate,"Stocktake", ProductID, Quantity
    FROM StockTakes AS ST1
    WHERE StockTakeDate=
        (SELECT MAX(StockTakeDate)
          FROM StockTakes AS ST2
          WHERE ST2.ProductID = ST1.ProductID);
    UNION ALL
    SELECT OrderDate,"Sale", ProductID, Quantity*-1
    FROM Orders INNER JOIN OrderDetails
    ON Orders.OrderNumber = OrderDetails.OrderNumber
    WHERE OrderDate >=
        NZ((SELECT MAX(StockTakeDate)
         FROM StockTakes
         WHERE StockTakes.ProductID = OrderDetails.ProductID))
    ORDER BY ProductID,AcquisitionDate;

    This returns all movements into and out of stock, the former as positive integer numbers, the latter as negative integer numbers.  From this query, and the Calendar table the stock in hand per product at close of business on each date in the Calendar table can be returned with the following query:

    SELECT calDate, ProductID,
        (SELECT NZ(SUM(Quantity),0)
         FROM qryStockMovements
         WHERE qryStockMovements.ProductID = Products.ProductID
         AND qryStockMovements.AcquisitionDate <= Calendar.calDate) AS StockInHand
    FROM Products,Calendar
    GROUP BY ProductID,calDate;

    In reality you would probably want to restrict the result table to a date range by means of parameters on the calDate column.

    For means of creating auxiliary calendar tables see Calendar.zip in the same OneDrive folder.  The option to create a basic daily calendar would be used.  You can if you wish omit weekends and remove public holidays from the table (having first recorded them in the PubHols table), so as to include only working days.

    Ken Sheridan, Stafford, England

    Wednesday, March 21, 2018 12:20 AM