Results 1 to 10 of 10
  1. #1
    koturtle is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    7

    Find Closest Lowest Number

    Probably a bad title -
    Here is what i am trying to do:

    I am trying to create a database that will look up a value(number) in one table (tableA) and return the record from another query (QueryA) that is closestto that number without going over.

    My record in table A has a field called [Qty Needed] with value 15

    I would like it to look at the Qty in Query A below andreturn the closest without going over to 15. So it would return the Qty 10 for a $2 price.


    Query A
    Part number ------Qty ------- Price
    Xyz -------------- 1 ---------------5


    Xyz ---------------5 ---------------3
    Xyz --------------10--------------- 2
    Xyz------------------- 20 -------------------1

    TIA,
    KO

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Something like Dmax("Qty", "QueryA", "Qty<" & dlookup ("[Qty Needed]", "tableA", PUT YOUR TABLE LOOK UP CRITERIA HERE))

  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
    Generically

    SELECT TOP 1 Price
    FROM TableName
    WHERE PartNumber = 'xyz' AND Qty <= 15
    ORDER BY Qty DESC

    More info about how/where you want the value might lead to a more specific answer. I might create a function that is passed a part number and quantity and returns the price, using that SQL in a recordset.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    The text box on the form would have the qty needed....

    Q1:
    Select part,max(Qty) from Table1 where [Qty]< forms!fMyForm!txtQtyNeeded

  5. #5
    koturtle is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    7
    Thank you, i believe this will work!

  6. #6
    koturtle is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    7
    Quote Originally Posted by kd2017 View Post
    Something like Dmax("Qty", "QueryA", "Qty<" & dlookup ("[Qty Needed]", "tableA", PUT YOUR TABLE LOOK UP CRITERIA HERE))


    This seems to return the highest value in the table and notthe highest record for each part number.

    Let me try to explain better:


    Table A
    Part Number – Total Qty Need

    XYZ ---------- 6

    ABC ------------ 10



    Query A

    Part Number ------- Qty ---------- Unit Price
    Xyz ------------- 1-------------------- $20
    Xyz -------------- 2-------------------- $15
    Xyz --------------- 5--------------------$10
    abc ------------- 4-------------------- $25
    abc -------------- 10-------------------- $15
    abc --------------- 15--------------------$8

    Desired result:


    Part ------- Qty ----Unit Price

    Xyz -------- 5 ------ $10

    ABC -------- 4---------$25







  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I see, you're looking for a query that will return a record for each part number. I have some questions about your sample data. In [Query A] can there be more than one record with the same [Part Number] and [Qty] combination? If so do you want to return all matching records or may just the one with the highest or lowest unit price? Can you also provide the data behind [Query A] comes from? Finally, I think you must have a typo in your desired results for the ABC row..?

    There may be a better way to do this:
    Code:
    SELECT subquery.[part number], 
           [query a].qty, 
           [query a].[unit price] 
    FROM   (SELECT [query a].[part number], 
                   Max([query a].qty) AS MaxOfQty 
            FROM   [table a] 
                   INNER JOIN [query a] 
                           ON [table a].[part number] = [query a].[part number] 
            WHERE  (( ( [query a].qty ) <= [table a].[total qty need] )) 
            GROUP  BY [query a].[part number]) AS subquery 
           INNER JOIN [query a] 
                   ON ( subquery.[part number] = [query a].[part number] ) 
                      AND ( subquery.maxofqty = [query a].qty );

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Okay I think I have a better understanding what you're trying to do. [Query A] is a pricing structure, eg if you order 5 number of items it costs x per unit, if order 10 or it only costs y per unit kind of thing. Here is another query that returns the [Part Number] and [Qty Needed] from the actual order quantities, and the [Unit Price] at that pricing tier.

    Code:
    SELECT [order qtys].[part number], 
           [order qtys].[total qty need], 
           (SELECT TOP 1 [pricing tiers].[unit price] 
            FROM   [pricing tiers] 
            WHERE  [pricing tiers].[part number] = [order qtys].[part number] 
                   AND [order qtys].[total qty need] >= [pricing tiers].[qty] 
            ORDER  BY [pricing tiers].[qty] DESC) AS [Unit Price] 
    FROM   [order qtys]; 

  10. #10
    koturtle is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    7
    Quote Originally Posted by kd2017 View Post
    Okay I think I have a better understanding what you're trying to do. [Query A] is a pricing structure, eg if you order 5 number of items it costs x per unit, if order 10 or it only costs y per unit kind of thing. Here is another query that returns the [Part Number] and [Qty Needed] from the actual order quantities, and the [Unit Price] at that pricing tier.

    Code:
    SELECT [order qtys].[part number], 
           [order qtys].[total qty need], 
           (SELECT TOP 1 [pricing tiers].[unit price] 
            FROM   [pricing tiers] 
            WHERE  [pricing tiers].[part number] = [order qtys].[part number] 
                   AND [order qtys].[total qty need] >= [pricing tiers].[qty] 
            ORDER  BY [pricing tiers].[qty] DESC) AS [Unit Price] 
    FROM   [order qtys]; 
    This worked perfect! thanks for your help

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

Similar Threads

  1. Replies: 1
    Last Post: 02-08-2015, 08:11 PM
  2. Find Variables closest to specified Date
    By crimedog in forum Reports
    Replies: 1
    Last Post: 01-30-2014, 11:10 AM
  3. Using expression builder to find lowest date
    By MMcKenna in forum Queries
    Replies: 1
    Last Post: 03-20-2012, 02:28 PM
  4. find the closest year
    By 12345678 in forum Queries
    Replies: 1
    Last Post: 05-25-2011, 05:07 AM
  5. Query to find lowest value
    By AccessNubie in forum Access
    Replies: 3
    Last Post: 11-30-2009, 07: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