Results 1 to 5 of 5
  1. #1
    janbrown56's Avatar
    janbrown56 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    18

    Question Need to check calculatioin on previous record in report

    I need some help for a query and report I'm working on. It's a report to tell us how many bins of product to produce based on the forecasted bin demand less what's already been produced. We have material in different lots and the lots are listed in FIFO order.



    Example 1 - SBSNo .5C35MSC:If the first lot number for a part has enough OnHandQty to cover the Production Shortage I do not want the next lot to appear. So when I'm running my query I want to check the shortage sub-total of the previous 'record'.

    In my report below the first part has 2 lots listed. The Production Shortage is 7,700 pieces. The first lot listed, 7082195, has plenty OnHand Qty to fulfill that shortage. The Bin Qty is 700 pieces so they need to produce 11 bins for the total shortage of 7,700 pieces. When I am looking at the next lot, I want to be able to see the Shortage sub-total o the previous record, in this case it would be zero - no more bins need to be produced. That means that the second lot is not necessary and I do not want it to be in my report.


    Example 2 - SBSNo .8C50MSF-: The Production Shortage is 900 pieces. Bin Size is 300 pieces so a total of 3 bins are necessary. The first lot, 461348, only has 800 pieces so this lot has enough for only 2 bins. The Production Shortage sub-total is 300. That means I want to show the second lot, 505412, for 1 bin needed.


    I use a report so the SBSNo doesn't repeat but if I can change the logic so it only shows the lots that are necessary I can do this in a query instead.

    How can I accomplish this in a query or report?

    Thank you in advance for your brain-power!

    Jan
    Click image for larger version. 

Name:	2016-06-23_8-33-15.jpg 
Views:	13 
Size:	121.3 KB 
ID:	25002

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm afraid that in trying to follow the numbers/quantities that I don't get the logic of things like how you are 300 short when you need 900 but have 800 on hand. No matter; the problem reminds me of a similar situation where I had to perform calculations on fields between rows and include/omit rows in the report based on the calculation.
    My solution was to put the primary records into a temp table, then in a function, move through the rows, then
    - assign value on row 1 to a variable, move next and get the value
    - assign value on row 2 to a variable and do the calculation
    - if it met the test, move previous and set the flag (checkbox field) to true then move(1)
    - the top of the loop moved next, reset the variables and the comparisons started over
    - the report was filtered on true flags
    - the function returned true/false if all went well/didn't
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    janbrown56's Avatar
    janbrown56 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    18
    Thanks Micron,

    I know it's confusing ... 900-800=100 but I put 300 shortage. I'm concentrating more on the number of bins rather than the actual pieces.

    I appreciate your reply but unfortunately I do not understand how to accomplish your suggestion.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please post a copy of the database (zip format) and clear instruction to get to the FIFO logic/algorithm.

    Have you tried working with a copy of your database and only a few records so that you can follow the calculations while stepping through the code?

    Do you have queries that you can manipulate to get the values you need? That is can you use your FIFO calculations and get the correct values in a query? (just trying to see if it is the FIFO calculation, or the report that may be part of the issue).

    Did you create the database?

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I do not understand how to accomplish your suggestion
    Let's wait and see if others who do understand (and there will be some, I'm sure) have a better idea.

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

Similar Threads

  1. Replies: 1
    Last Post: 04-15-2014, 06:06 PM
  2. shows previous record on report.....
    By shah1419 in forum Reports
    Replies: 0
    Last Post: 08-28-2013, 02:22 AM
  3. Replies: 5
    Last Post: 04-08-2013, 09:04 AM
  4. need to check a field for previous record in form
    By clemdawg in forum Programming
    Replies: 1
    Last Post: 06-13-2012, 07:17 PM
  5. check textbox1 on each record of report
    By sconard in forum Access
    Replies: 8
    Last Post: 04-13-2011, 12:26 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