Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    UncleKay is offline Novice
    Windows 10 Access 2003
    Join Date
    Feb 2016
    Posts
    25

    Arrow Stock aging code

    Dear friends,
    Please I need appropriate code for stock aging report of the following ranges:
    1. Less than 3 months
    2. 3 months to 6 months
    3. 6months to 1 year
    4. 1 year to 2 years
    5. 2 years to 3 years
    6. Greater than 3 years
    The code I am using is not giving me the anticipated results.


    Kindly assist

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're asking for someone to write code without having any knowledge of the field/control or whatever, names.
    Post what you have tried and did not work - please use code tags (forum toolbar).
    Explain what's happening (doesn't work doesn't help); give error messages and text if applicable.
    Explain what you want. When you think you have it right - read it again before posting.
    Then we have a chance of helping.

    You have two submissions for the same problem. Mark the other one as solved 'cause I don't think you can delete your thread.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Micron View Post
    You have two submissions for the same problem. Mark the other one as solved 'cause I don't think you can delete your thread.
    I can.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try google to get some appreciation of the logic involved; and search for FIFO Inventory/ stock control

    Much of the Stock Aging info from Google search is for SAP.

    Here is a response in an Oracle related link

    Please show readers what you have tried and the results.

    Good luck.
    Last edited by orange; 03-30-2016 at 08:21 PM.

  5. #5
    UncleKay is offline Novice
    Windows 10 Access 2003
    Join Date
    Feb 2016
    Posts
    25

    Update

    Dear Friends,
    I sincerely apologize as follows:
    1. Posting the same thread twice. I went back after the observation and marked one solved as suggested but could no longer see the second one. I re-enabled this one to respond.
    2. For not giving details of the code I have used, what I have tried and the problems I encountered.
    While apologizing for defaulting, please note that I am relatively new to this forum and did not intentionally break the rules.
    I therefore give the details below:
    All the relevant details are contained on the three attached files to this post.

    MY MAJOR PROBLEM IS THAT THE GRAND TOTAL ON THE AGING REPORT DO NOT CORRESPOND TO THE GRAND TOTAL OF THE STOCK SUMMARY ACCORDING TO THE CLIENTS REQUIREMENT.

    I have a feeling that the query code I used may not be correct because the RECEIPT QUANTITY for the aging ranges are not considered.

    Is there any code approach I can apply to solve this need?

    Thank you for your patience.
    Attached Files Attached Files

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you tried working through your code systematically with say 3 products? Use a sample set that is manageable that you can "desk check", then gradually increase volume.

    I note that you have 5 posts in the forum, but it seems this isn't your first "kick at the cat" for stock management and reporting, nor database/Access or sql. It seems you have most everything under control.

    Do you have a clear definition of all terms?
    Do you have arithmetic/logic statements identifying/specifying the required calculations?

    I ask this because I have not found much information in simple terms. There seems to be more info related to SAP and other software products.

    If you are saying that you have not included the quantities of any "received products" in the calculations, then it would seem that StockOnHand would be understated.

    ...based on this sort of thinking

    StockOnHandNow =StockOnHandLastCheck + StockReceivedSinceLastCheck -StockSoldSinceLastCheck

    I emphasize that, other than responding to posts on some forums, I am not "hands on" familiar with Stock/Inventory Management nor Ageing.


    Good luck with your project.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I would agree that one or both of the queries behind these reports are not returning the correct results so a careful examination of why is required.
    Stock Aging Summary shows qty 2 for 11196-08, the Stock Summary qty = 0.
    SAS for 13184-01 shows qty 1 ($680.38); SS = 3 for total $2041.14
    HOWEVER, the other units are missing from the age report. So in one case it appears items are being left out in both reports.

  8. #8
    UncleKay is offline Novice
    Windows 10 Access 2003
    Join Date
    Feb 2016
    Posts
    25
    1. The Stock on hand includes received quantity but the aging within the range only makes use of the quantity issued.
    2. I have tried checking the report with fewer items and at a stage, I started noticing the different totals and I do not know where this difference is coming from
    3. Is there any other stock aging formula of the ranges under consideration you know that I can try out or is my approach the best from your experience?
    Your further assistance will be appreciated

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I noticed you had another post regarding queries related to aging where a discrepancy was identified.
    Is this current post a continuation of the discrepancy(s) found there?

    Can you provide a copy of the database and just enough data to run/analyze / review your calculations/results?

    I just found this not sure if it helps.

    I am not clear how you are tracking the inwards and outwards based on the above table design. To calculate stock ageing, you need to be able to track each inward separately and track all outwards from this particular inward. To give an e.g. if you receive Material A on 13 Jan 2009 Qty 50, from this stock you dispatched Qty 20 on 17 Jan 2009, Qty 1 on 20 Aug 2009 and Qty 3 on 31 Dec 2009. Now you have Qty 26 in stock from the inward of 13 Jan 2009. If you generate the ageing report in Jan 2010, it should show you you have Qty 26 of almost an year-old stock lying with you. I am not sure how you are storing this info in your design.

    Unless you are able to track how much quantity from each inward is left in the stock, I don't think you would get an accurate ageing report.
    More info:
    There was a Batch allocation (FIFO) question here. The dialog may be of value. I participated to a degree, as did others, but I don't know that all issues (requirements seemed to expand throughout) were settled/resolved.
    It's been almost 2 years since that exchange so it's foggy to me at best. I know that it did not deal with stock aging explicitly, but you may see components that provide some insight to your issue.
    But, then again, it may be a different subject.

    Good luck with your project.
    Last edited by orange; 04-02-2016 at 08:05 AM.

  10. #10
    UncleKay is offline Novice
    Windows 10 Access 2003
    Join Date
    Feb 2016
    Posts
    25
    Yes. the last post is a clarification of the outlined discrepancies.
    I will reduce the entries in the database to manageable records and try sending the application if the size can be uploaded.
    I have seen the link on the stock aging calculations. I will study it more closely to see if any of the examples can meet my needs.
    So far, I sincerely appreciate your ceaseless efforts towards this issue.
    Thanks a million

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I just opened a copy of the database Fifo Batch Allocation JED.mdb. (posted in linked thread)
    It certainly has no comments within, and it seems the original programmer may have had a fortran background with 2 and 3 character names. He also uses field level lookups that I find misleading and confusing. As per one of the older posts, I started to add some comments. But it was an one day "good intention" thing in Aug 2014 - I see I added 2 or 3 comments in anew table. I have not been near the code since. When I get a chance I will look at it again and see if I can make sense of it and add some comments/documentation. But that is a different project.

    I'll look at your materials, but offer no guarantees since Stock/Inventory Management and Logistics is not my area. I'm still trying to sort out what Stock Aging means in everyday terms.

    Note: You should make a sample database with enough data to show the issue. Then Compact and Repair, then make a backup for safe storage/recovery. Take a copy and zip it. Post the zip and any relevant documentation/facts to help readers get to the issue.

    Good luck.

    After posting: I checked back on the linked thread I mentioned. There was a link to the original post concerning
    FIFO Batch Allocation. It was by khwar Batch allocation to sales on fifo basis

    and is located at
    http://www.access-programmers.co.uk/...d.php?t=153802
    So that is where the original db came from.

    I have a couple of similarly named files [downloaded] (trying to sort them out). Both deal with FIFO.

    The other file is from this May 2012 thread on FIFO Inventory.
    https://www.accessforums.net/showthread.php?t=25249
    Last edited by orange; 04-02-2016 at 10:52 AM.

  12. #12
    UncleKay is offline Novice
    Windows 10 Access 2003
    Join Date
    Feb 2016
    Posts
    25

    Update 07-04-16

    Dear Friends,
    As requested, I have been trying to upload and send a copy of the software. After zipping, I had 15MB. I have tried attaching the 15MB but it has not been possible. I want to remove some tables, forms, queries and reports that is not connected to the report under consideration to reduce the size further. As soon as I can upload the application, we will continue the troubleshooting from there.
    I appreciate your assistance and understanding.
    Thank you.

  13. #13
    UncleKay is offline Novice
    Windows 10 Access 2003
    Join Date
    Feb 2016
    Posts
    25
    Dear friends,
    I have done all I could to reduce the size of the zipped copy of the application to the recommended 2MB in the group as requested to no avail. The least it could get too after removing unrelated tables, forms, queries and reports is 14MB. What do I do to get the sample software across to enable us continue the troubleshooting on my complaints regarding the stock aging issue?
    Thank you for your continued assistance

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Did you do a compact and repair, then zip? 14-15 mb seems large

    (Possibly)You could start with a new empty database, and import the structures and just some data/stock records.

  15. #15
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    How large is it before you zip? That might give us some indication of how well (or not) that's working. Maybe, as orange is suggesting, you didn't compact it from some huge size first. If all else fails, you could put it on a file hosting website like Google drive.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Aging Report
    By Jblackbelt in forum Reports
    Replies: 10
    Last Post: 10-08-2014, 08:02 PM
  2. Case Aging report
    By mavisyew in forum Reports
    Replies: 1
    Last Post: 09-02-2014, 05:35 AM
  3. Designing A/R aging Form
    By Ray67 in forum Database Design
    Replies: 22
    Last Post: 08-21-2014, 06:07 PM
  4. Aging A/P function
    By nim73 in forum Programming
    Replies: 0
    Last Post: 05-07-2009, 01:23 PM

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