Results 1 to 12 of 12
  1. #1
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108

    Hiding / showing rows based on calculated columns within the report

    I have a report that is showing me when an item is becoming negative (supply & demand) and suggesting how much to order.

    These columns are both calculated (Balance and Suggested Reorder Qty) and I need all the data in the source table to calculate them. I can't calculate them outside the report either because the calculations are based on multiple groupings in the report that are necessary for the calculations to work.

    However I would like to only show the rows where my calculated column "Balance" is negative, basically only showing me the lines I need to reorder.

    Is there a way to filter accomplish this? Thank you.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,900
    Why can't you do all the calculations in underlying queries, and use that query as the Data Source of your Report?
    You can easily group records in queries using Aggregate Queries.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Because I do not know how to build such a query but I know how to group in a report.

    The table contains item ID's and various information that relate to each ID such as vendor, inventory unit, lead time, price etc.

    It also contains, supply, demand and current stock for each item.
    There are multiple rows for each items, for example I will have 1 row that contains the current stock, multiple rows that contain demands and when that demand is for (date) as well as supplies with when that supply is happening (date).

    So in my report, in order to calculate the balance (stock + supply - demand), I first need to group all these rows by vendor, then by item, then sort them by date, then I can calculate the balance as a running sum in that order to show me what line / date I am becoming negative. From there it also calculates what I should order based how much we are negative, my MOQ and multiple order qty.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,900
    That is exactly what you do with them (Aggregate Queries), group the records by certain fields, and then apply some mathematical computation to the group (i.e. Sum). They are pretty easy to do, once you know how.
    There are lots of articles and tutorials that can be found with a Google Search, whether you prefer some sort of text document, or some YouTube video. They do a much better job of explaining it and walking you through it than I could do here.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  5. #5
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Ok, I'll try and see if I can make it work that way.

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,900
    OK. If you get hung up on any part of it, feel free to post back here and we will see if we can help you work through that part.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  7. #7
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Aggregating the data is easy enough and they appear like in my report. Where I'm having trouble is with the running sum, I can't figure out how to use DSum() to calculate my balance

    In my query results should look like this

    Code:
    Vendor_Name Component_ID Current_Stock Supply Demand Planned_Date Balance
    VendorX Item123 0 44000 0 16-Aug-19 44000
    VendorX Item123 0 0 43554 21-Aug-19 446
    VendorX Item123 0 0 518.5 01-Sep-19 -72.5
    VendorX Item123 0 0 31110 01-Sep-19 -31183
    VendorX Item456 27066 0 0 27066
    VendorX Item456 0 20000 0 24-Jul-19 47066
    VendorX Item456 0 0 27000 26-Jul-19 20066
    VendorX Item456 0 0 20000 06-Aug-19 66
    Except "Balance" is empty at the moment

    Balance is a running sum of [Current_Stock]+[Supply]-[Demand]

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,900
    I think the way about this is to create a calculated field that adds up the three fields for each row, and then add the Running Sum directly on the Report, as explained here (see the "Calculate a running sum" section):
    https://support.office.com/en-us/art...3-b8ae9639fbf4
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  9. #9
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    That would work that way indeed.
    However, I would still need to run that DSum() in the query rather than in the report, with the goal of only showing in the report where "Balance<0"

    So if I call the row sum line balance LBalance: stock+supply-demand, then I need to have another field "Balance" to run the DSum() based on LBalance, then I can build the report with only the negative values of "Balance"

    In my example, only Lines 3 and 4 should show in the report.

    The expression should look like something like this
    Code:
    Balance: DSum("[LBalance]","[Component_ID]=" & [Component_ID])
    = run sum on LBalance as long as Component_ID from previous row = Component_ID of current row. But this does not work as is.

  10. #10
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Finally I made it.

    It's not pretty because it forces to create another table, I used the explanations from this post

    https://theaccessbuddy.wordpress.com...a-part-2-of-2/

    Thanks for your help.

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,900
    Great! Glad you got it working.

    Going along my original line of thought, I might try to take a little different approach, for determining which records to show (there are often multiple ways to solve a problem).
    Create another aggregate query which does the total sum of your groupings, and use criteria to filter out the totals you don't want to see.
    Then, link this query to your other grouped query with the details to only return the records you want on your report.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  12. #12
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Quote Originally Posted by JoeM View Post
    Great! Glad you got it working.

    Going along my original line of thought, I might try to take a little different approach, for determining which records to show (there are often multiple ways to solve a problem).
    Create another aggregate query which does the total sum of your groupings, and use criteria to filter out the totals you don't want to see.
    Then, link this query to your other grouped query with the details to only return the records you want on your report.
    Yes I still have some things to work out on this. As of now it's showing be the negative lines and for each negative line it's suggesting the order quantity. This is not exactly correct yet because it should be taking in account the previous suggested quantities. For example if I am at -500 and the MOQ is 2000, it will suggest ordering 2000. Next line if I am -1000 (-500 for the previous line and -500 for the current line), it will also suggest to order 2000 while it fact it should suggest nothing because if I order 2000 for the first negative line, I'll have 1500 in stock which is enough to cover the following -500. Therefore it should not even show that second line. But if there was a third negative demand, like -2500 at a different date, then it should suggest replenishment on that line again.

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

Similar Threads

  1. Replies: 5
    Last Post: 06-05-2018, 07:06 AM
  2. Replies: 1
    Last Post: 07-30-2013, 06:33 AM
  3. Showing Columns & Rows in Crosstab query
    By coach32 in forum Queries
    Replies: 6
    Last Post: 09-11-2011, 07:01 PM
  4. Replies: 2
    Last Post: 08-29-2010, 01:17 AM
  5. Hiding rows when blank in a report.
    By ser01 in forum Reports
    Replies: 0
    Last Post: 02-27-2010, 10:29 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