Results 1 to 6 of 6
  1. #1
    Oran12 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    2

    Display records that meet calculation criteria on different lines

    Hi there!
    Please assist me :


    I have 4 columns . i want to do my calculation in the 5th column "Ship"
    "total Allowed Qty for product: is the total qty that is allowed to be shipped to the stores.
    "Product" changes all the time
    "Location" is what stores the products go to. Note this also changes depending on the product
    "Expected Qty" is the what the store can expect.
    However we cannot ship more than the "total allowed qty for product":

    Ship: is where i need to display the correct value to ship:
    How do i write code in ms access 2016 to do a calculation that says:
    if the "expected qty" is < "total Allowed Qty for Product", write the "expected qty" for the first line for product A1.
    Second line, if the first line in "Ship" is < "total Allowed Qty for Product" and"Expected qty" is > than the difference, "total Allowed Qty for Product"-line 1 in Ship .
    Display 0 if we met the criteria where we reached "total allowed qty for product"
    total Allowed Qty for Product Product location Expected Qty Ship
    6 A1 Store 1 3 3
    6 A1 Store 3 4 3
    6 A1 Store 4 1 0
    6 A1 Store 9 8 0
    6 A1 Store 16 5 0
    10 B1 Store 2 5 5
    10 B2 Store 3 4 4
    10 B3 Store 4 6 1
    10 B4 Store 12 2 0

    Do i use a running total? If so , how will i code it?
    Thanks in advance

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    running sums only apply to reports - and you have not said whether this is for a report or a form

    you need to clarify a number of other things as well
    1. based on your comments and example data, why can store 4 only have 1 B3 when 10 are allocated and they expect 6?
    2. you are showing the outcome you require - you are not showing what your tables look like to start with. I would expect at least three tables, probably more
    3. you also need to explain what basis you are using to define 'first row'. It would appear to be the alphabetical order of location name by product, but that does not make sense in the real world, the lower numbered locations will always take priority if that was the case.
    4. your requirement seems to assume that you will be shipping to a maximum of 2 stores for any allowed quantity - again, seems an unlikely real world situation
    5. does your final result need to be updateable? i.e. you want users to be able to modify or add to the data

  3. #3
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Maybe the correct table should be

    total Allowed Qty for Product Product location Expected Qty Ship
    6 A1 Store 1 3 3
    6 A1 Store 3 4 3
    6 A1 Store 4 1 0
    6 A1 Store 9 8 0
    6 A1 Store 16 5 0
    10 B1 Store 2 5 5
    10 B2 Store 3 4 4
    10 B3 Store 4 6 6
    10 B4 Store 12 2 2

    since B1, B2, B3 and B4 are different products.

  4. #4
    Oran12 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    2
    Quote Originally Posted by Ajax View Post
    running sums only apply to reports - and you have not said whether this is for a report or a form

    you need to clarify a number of other things as well
    1. based on your comments and example data, why can store 4 only have 1 B3 when 10 are allocated and they expect 6?
    2. you are showing the outcome you require - you are not showing what your tables look like to start with. I would expect at least three tables, probably more
    3. you also need to explain what basis you are using to define 'first row'. It would appear to be the alphabetical order of location name by product, but that does not make sense in the real world, the lower numbered locations will always take priority if that was the case.
    4. your requirement seems to assume that you will be shipping to a maximum of 2 stores for any allowed quantity - again, seems an unlikely real world situation
    5. does your final result need to be updateable? i.e. you want users to be able to modify or add to the data
    Hi There!
    My apologies
    1. @CarlettoFed i have 2 products, so the correct table should be :
    total Allowed Qty for Product Product location Expected Qty Ship qty
    6 A1 Store 1 3 3
    6 A1 Store 3 4 3
    6 A1 Store 4 1 0
    6 A1 Store 9 8 0
    6 A1 Store 16 5 0
    10 B1 Store 2 5 5
    10 B1 Store 3 4 4
    10 B1 Store 4 6 1
    10 B1 Store 12 2 0

    2. I am using ms Access 2016.
    My data comes from excel sheets which i query only the necessary information.
    Query1:"Product", "store"and "expected qty" from Excel sheet1
    Query 2 : "total Allowed Qty for Product" From excel sheet 2
    Query 3 is the table you see above. I linked it by product. Now i need to calculate the "Ship qty"


    3. I want to calculate the "ship qty" for each product. This was just an example, I have many products and the "total Allowed Qty for Product" will be updated when the user updates it in excel.
    The "ship qty" cannot exceed the "total Allowed Qty for Product" for each product.
    Basically, I want to say which stores must i ship to if I only have a certain amount ("total Allowed Qty for Product" ).
    Afterwards i want to remove the zeros in "ship to" and only display the necessary stores to ship to.

    5. yes the user will update both sheets in excel i.e. :
    "total Allowed Qty for Product" in excel sheet 1
    Product, store and expected qty in sheet 2.

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Wouldn't it be more simple to program the whole calculation in Excel? After all there you have a specific order in the lines, which doesn't exist in Access tables.
    If updates are made in Excel, for what part do you use Access?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you know your business, we don't. So you need to be very clear about what you have

    2. you are not showing what your tables look like to start with, you are just describing them. Provide the data in table format that result in your 'query3'

    please also answer questions 3 and 4.


    yes the user will update both sheets in excel i.e. :
    "total Allowed Qty for Product" in excel sheet 1
    Product, store and expected qty in sheet 2.
    I presume this is a manual operation since I don't see any reason for updating the total allowed quantity

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

Similar Threads

  1. Replies: 7
    Last Post: 02-19-2019, 01:26 PM
  2. Replies: 1
    Last Post: 03-02-2014, 03:25 PM
  3. Only show records that meet criteria
    By RussH in forum Reports
    Replies: 9
    Last Post: 04-10-2013, 05:25 AM
  4. Query for records that do not meet criteria
    By survivo01 in forum Queries
    Replies: 3
    Last Post: 12-16-2012, 05:45 PM
  5. Summing when no records meet criteria
    By clew3 in forum Queries
    Replies: 6
    Last Post: 11-05-2012, 11:37 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