Results 1 to 5 of 5
  1. #1
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89

    Help with query criteria - Logic and mathematical comparisons

    Hi again folks,


    Hopefully I'm in the right forum, I'm scratching my head over this one:
    BCMRP1.zip
    In the attached file I have a query, qryMTLPriceSelector. This query, and others related to it, are being used to winnow down a list of prices for an assembly. The pricing is based on how many assemblies are required and I am trying to have this query output only the rows where the Diff field has the greatest value that is less than zero. I had some functionality by adding criteria to the Diff field in a second query that pulls from this one to limit the results to only those less than zero and greater than the inverse of the PriceQTY field, but as the quantity of assemblies in the field RFQQTY increased I began not seeing the results for the larger numbers as they fell outside of the criteria just described.
    I've tried using MIN and MAX, but the challenge is that I need one row for each unique RFQQTY. Any thoughts?

    Please let me know if I've been too vague, happy to provide more info.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You need 2 queries:
    Q1 to get the min.
    Select id, min(field) from table.

    then Q2 to get the record..
    select table.* from table, Q1 where table.ID= Q1.ID.

  3. #3
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Quote Originally Posted by ranman256 View Post
    You need 2 queries:
    Q1 to get the min.
    Select id, min(field) from table.

    then Q2 to get the record..
    select table.* from table, Q1 where table.ID= Q1.ID.
    Thanks for responding, I'll try this out. I may be back with further questions!

  4. #4
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    OK, thanks again for the reply. I am playing around with the suggestion and not getting too far.
    The tables that I am pulling data from don't have what's necessary to build two queries to achieve the goal, as far as I can tell, because the query needs to examine a calculated value.
    I'll try to explain a bit more: I have assemblies, each of which comprises several materials. The goal of the query is to generate a price for the assembly. Because there are bulk discounts associated with the materials (Each bulk discount is stored in tblSourcing with a sourcing ID) the number of assemblies (Number of assemblies is defined and stored as RFQQTY) affects the price. The goal of the query is to put the right SourcingID with the right RFQQTY, which requires some calculations, such as the total amount of material needed for that quantity of assemblies and whether or not that quantity is below the minimum order threshold for that material. In the existing query I have the field 'Diff', which is the difference between the quantity needed for that number of assemblies and the price for any given bulk discount. This is what I'm trying to refine - that value should always be less than or equal to zero, but as the number of assemblies increases the number of bulk discounts whose 'Diff' value is less than or equal to zero increases. I'm hoping to get one line for each material ID for each RFQQTY ID, so if there are two materials then, for each RFQQTYID there should be two lines. The query that comes after this totals materials. So, qryMtlQTYRFQQTY gathers all the information and does some of the calculations, the query qryMTLPriceSelector chooses from that data based on the 'Diff' field. If I set the criteria at less than or equal to zero I get too many results. I tried narrowing it down by adding that the value of 'Diff' should also not be less than the inverse of the field TotalMtlPerRFQQTY, but as the number of assemblies increases that criteria gets violated and some rows begin not showing up.

    This is a bit of a mind bender for me as I can select the correct value looking at the data, but I can't seem to come up with criteria that express the logic of that selection that don't return too many or too few values.

  5. #5
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89

    A messy solution?

    BCMRP1_2019-12-13.zip
    I have found something that worked. Rather than two queries it took three. For some reason I could get a query to pull up the minimum values as suggested, but the values I needed were the maximum values that were equal to or less than zero and when I set it up for that it would not pull the necessary values. Min worked, Max did not, so I added an additional query that produces the ABS value of the numbers and then ran the Min and got what I needed. From there I was able to select the records that matched the Min*-1.

    Attached the functioning solution here. qryMtlQTYRFQQTY calculates out all the possible material for an assembly. This feeds MtlPriceSelector, which determines if the necessary materials are less than the minimum order qty for that material. It also feeds qryDiffInverter and then qryDiffComparator. DiffComparator also is fed by MtlPriceSelector, and then into qryMinMatcher which finally feeds qryAssemblyPricing.

    This provides the right results for this set of data, the last time I thought I had a solution to this it failed when the set of data grew, so I'm hopeful, but prepared for the eventuality that this may not function in all cases. Also, I feel what I have is very messy so I am open to suggestions on cleaning this up - I feel as though perhaps I have at least two too many queries in this process...

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

Similar Threads

  1. Date comparisons
    By joethall in forum Access
    Replies: 2
    Last Post: 05-21-2017, 07:53 PM
  2. Mathematical calculation
    By j235 in forum Access
    Replies: 1
    Last Post: 11-18-2014, 11:24 AM
  3. Mathematical comparison operator in query
    By kazaccess in forum Queries
    Replies: 13
    Last Post: 07-22-2013, 01:25 AM
  4. Weekly reporting comparisons in query
    By Schon731 in forum Queries
    Replies: 3
    Last Post: 10-21-2010, 06:59 PM
  5. Adding Mathematical Operators to my QBF
    By Silver Rain 007 in forum Queries
    Replies: 1
    Last Post: 11-05-2009, 08:54 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