Results 1 to 3 of 3

Silly question... Return lowest value higher than X OR highest value in table?

  1. #1
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    8

    Silly question... Return lowest value higher than X OR highest value in table?

    I have a price table based on the quantity of items so I have to find the row with the lowest quantity value that is greater than X. That was pretty easy, just using ORDER BY to have the lowest qty be the first row.

    Where my brain is melting is when X is higher than the highest quantity in the table and I need that last row.

    So if the table has three fields ID, Qty, and Price

    4 | $15
    6 | $12.50
    12 | $10
    24 | $7.50

    The basic query would be Select * FROM myTable WHERE Qty > myQty ORDER BY Qty



    If myQty is 25 I get nothing. I could use that and do another query to get the highest Qty row but I'm wondering if there's a way to return the highest row as a default?

    Thanks!

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,402
    select min([price]) FROM myTable WHERE qty> myqty

    EDIT: I misread your post the first time.

    if you want to do this in queries you may need a custom function to look up the number of records above a quantity first

    i.e. in your query have something like

    Code:
    Public Function MAXVAL (iItemID as long, qty as long)
    
    MAXVAL = dmax("[QuantityField]", "tblTableName", "[PK_Field] = " & iitemid)
    if maxval > qty then maxval = qty 
    
    end function
    then in your query you could have a formula like

    select min(Price) from mytable where qty >= maxval(ID, myqty)

  3. #3
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    8
    Thanks!

    For the moment I just check to see if 0 rows are returned then do another query to get the row with the highest Qty (lowest price).

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

Similar Threads

  1. Replies: 4
    Last Post: 04-04-2018, 09:14 AM
  2. Replies: 2
    Last Post: 12-29-2017, 11:01 AM
  3. Replies: 15
    Last Post: 12-22-2016, 12:16 PM
  4. Replies: 1
    Last Post: 10-05-2016, 08:20 AM
  5. Replies: 1
    Last Post: 02-08-2015, 08:11 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
  •  
Tech Forums: Microsoft Office Forums