Results 1 to 8 of 8
  1. #1
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127

    Return all records between first and last that meet criteria.

    I am trying to build a query (or two) that will return all the values between the first and last records that meet a criteria.

    Examples:



    ID___Start___End___Height
    1____0____5______50
    2____5____10_____100
    3____10___15_____40
    4____15___18_____80
    5____18___28_____65

    Where all rows between the first time height exceeds 75 to the last time it does. In this instance rows 2, 3, and 4 would be returned. Even though row 3 is below or equal to the threshold of 75, it is included because it's between other rows which are above.

    Thank you. I have tried DMin and using First/Last but I keep getting caught up trying to include all the records in between.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Records in tables don't have any specific order. You can order data from a table by means of a query with an Order By.
    If order has meaning to you then I suggest you consider a Datestamp on each record.
    Many people will not use First/Last.

  3. #3
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    The start and end are always ordered smallest to largest, is that enough?

    I do have datestamps however it updates every time the record is modified so I'd much prefer to use the start and end values.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try it and see if smallest to largest is sufficient for your needs. It may be.
    As for your datestamps, you might want to add a datestamp to record when record is created. Seems you have it for create or modify which is not what you want.

  5. #5
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    I can sort smallest to largest and query out everything >= 75 just fine. I don't know how to include all the records in between.

    The datestamp does work the way I want it to, but I use it for a different purpose. It's kind of a coincidence that it's in this table. Data is often also inserted later, so it's best to go with something other than date.

    How do I include all records between smallest and largest where my condition is true?

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have a look at the attache and see if that is what you need.

    Cheers,
    Vlad
    Attached Files Attached Files

  7. #7
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    Precisely. Thank you once more, Vlad.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're welcome!

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

Similar Threads

  1. Replies: 1
    Last Post: 03-02-2014, 03:25 PM
  2. Only show records that meet criteria
    By RussH in forum Reports
    Replies: 9
    Last Post: 04-10-2013, 05:25 AM
  3. Query for records that do not meet criteria
    By survivo01 in forum Queries
    Replies: 3
    Last Post: 12-16-2012, 05:45 PM
  4. Replies: 2
    Last Post: 11-28-2012, 10:47 AM
  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