Results 1 to 3 of 3
  1. #1
    AMJADJ is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jul 2018
    Posts
    34

    Remaining Stock Balances

    Apologies if this should be in another section, asi am building a report I put it here.



    I have a table which among other things contains product, order_no, date_reqd, order_qty and stock_qty. A product can have multiple orders on the system at once, but obviously there will only be one stock figure.

    So example data I have is:

    Product A order1 16/11/2018 200 600
    Product A order2 20/11/2018 300 600
    Product A order3 26/11/2018 175 600

    What I am trying to create is a report that will show me the "running" stock balance, taking into consideration each order, so a report using the data above, will show

    Product A order1 16/11/2018 200 600 400
    Product A order2 20/11/2018 300 600 100
    Product A order3 26/11/2018 175 600 -75

    -- > I am happy creating the report and grouping by product and date required to get things in the correct order (so I know when I will be running out of stock).
    -- > Not sure if it is required, but I have a running total of order quantities per product

    What I am unsure of is obviously the first line is stock - order qty but then how do I get the next line to remember the previous stock balance.

    Hope this makes sense, thank you for your help.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you use a group by query and sum the transactions by joining the table to itself

    transactions should all be in one table (receipts/sales/opening stock/stock adjustments, etc) so your table should look something like

    tblTransactions
    ProdName….TranFK…...TranDate……….Qty
    Product A....receipt1....01/11/2018....-600
    Product A....order1.....16/11/2018.....200
    Product A....order2.....20/11/2018.....300
    Product A....order3.....26/11/2018.....175

    then your query would join the table to itself, using aliases - something like


    Code:
    SELECT A.ProdName, A.TranFK, A.TranDate, Sum(B.Qty) as Balance
    FROM tblTransactions A INNER JOIN tblTransactions B ON A.ProdName=B.ProdName
    WHERE B.TranDate<=A.TranDate
    GROUP BY A.ProdName, A.TranFK, A.TranDate
    edit: if this is for a report, you can use the running sum feature in reports instead - but your table would still be the same as above.

    Note also, if you have two orders on the same date, then unless your date includes a time the query will put the same value in both rows - or you need some basis for determining order

  3. #3
    AMJADJ is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jul 2018
    Posts
    34
    Works an absolute treat.. just what I needed. thank you

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to create running balances
    By RonLB in forum Access
    Replies: 3
    Last Post: 03-05-2017, 10:51 AM
  2. PTO Balances and Accruals
    By breakingme10 in forum Access
    Replies: 5
    Last Post: 02-07-2017, 03:26 PM
  3. Replies: 4
    Last Post: 12-16-2016, 09:57 AM
  4. Query to get accounting balances
    By jobbie in forum Access
    Replies: 9
    Last Post: 03-09-2016, 05:56 AM
  5. Fee Calculation with Previous balances
    By jalals in forum Programming
    Replies: 14
    Last Post: 04-26-2013, 11:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums