Results 1 to 7 of 7
  1. #1
    BlueDemonLaw is offline Novice
    Windows XP Access 2013 64bit
    Join Date
    Apr 2017
    Posts
    3

    Minimum results return on Query and Top 1/3rd of return.

    Hello, as the title suggests I want to be able to have my form that is made to search for real estate and have a minimum threshold to bring back results.
    IE: If a person wants to search for red houses but there are only 4 in the entire county I want it to kick it back with a minimum threshold not being satisfied because my arbitrary number might be 5 results to make a general consensus on houses in that market.

    The same thing for a return for only the top 1/3th of results of the same kind of form.
    IE: if the results bring back 15 results I want it to eliminate 2/3rds of the results to show the top end of the market. It can be tied together so that it has to be 1/3th and also be more than 5.



    I hope that makes sense and thank you in advance for any advice, I am an Access novice and haven't worked with it extensively since high school back in the early 00's.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    You can find out how many records would be returned by using DCount() with the appropriate criteria.

    Not sure I understand your other requirement regarding 1/3rd and/or more than 5. How many records would be shown if 10 were returned and how many would be shown if 21 were returned.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    BlueDemonLaw is offline Novice
    Windows XP Access 2013 64bit
    Join Date
    Apr 2017
    Posts
    3
    Thanks for the response Mr. Fitzpatrick. In the return for top 1/3rd results for 10 would be invalid because I would want to return the top 1/3rd of a minimum that is at least 5. Thus the minimum would be 15 results to achieve that. (Bare in mind its just arbitrary numbers). So for 21 it would be a return of the 7 highest values.
    IE: Let's say I wanted to get all red houses in this county. I also want to use the most valued houses only.
    results are
    100
    100
    110
    110
    etc etc by intervals of 10 thru 21 results.
    the initial start I want is 15 to start a return because 1/3th of 15 would hit my arbitrary number of 5 returns minimum.
    So for my example the highest 7 (top 1/3rd) should only come back to the customer searching.

    Hope that made it more clear and thank you for the advice on the other part of my issue.

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Still unclear this end. Let's say a person is looking for a house with 3 bedrooms and a garage and there are 60 results (of varying values)

    How do you want your results. Do you want the top 20 results cheapest first?


    Sent from my iPhone using Tapatalk

  5. #5
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Seems like one way to go might be when a button is pressed,

    Select case dcount(field,table,criteria)

    Case <5
    Msgbox "message for not enough results"

    Case else
    Calculate number of results then create and run query in vba

    End case.






    Sent from my iPhone using Tapatalk

  6. #6
    BlueDemonLaw is offline Novice
    Windows XP Access 2013 64bit
    Join Date
    Apr 2017
    Posts
    3
    Thank you for the response Andy. 20 Cheapest is fine. So long as I can distinguish that whatever 1/3rd of the results I want can be shown with the search rather than the full results
    IE: most expensive 1/3rd or cheapest 1/3rd or closest 1/3rd by distance etc.

  7. #7
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    How many different options are there? You named 3. It might be easier to use a combobox with all the selections.


    Sent from my iPhone using Tapatalk

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

Similar Threads

  1. Return Minimum value between 5 fields
    By SamuelOu in forum Queries
    Replies: 3
    Last Post: 02-26-2015, 01:29 PM
  2. Replies: 5
    Last Post: 09-30-2013, 05:44 PM
  3. Replies: 7
    Last Post: 05-31-2012, 11:19 AM
  4. Replies: 2
    Last Post: 08-05-2011, 01:24 PM
  5. Replies: 5
    Last Post: 10-08-2009, 05:15 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