Results 1 to 8 of 8

Max Value is how much larger than 2nd largest number

  1. #1
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    120

    Max Value is how much larger than 2nd largest number

    I know how to find the maximum value in a table, but I don't know how to figure out how much larger it is than the 2nd biggest number.



    1
    2
    5
    7
    9
    3
    4

    It's easy enough to find the "9". What's more difficult is to know that it's 2 point higher than the 2nd biggest number in the table. I need to do a series of calculations like this on grouped numbers and in each case know how much larger the max number is than the 2nd highest number in the group.

    Any good ideas?

    Thanks

    Wayne

  2. #2
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,708
    Several methods e.g.
    Create a SELECT TOP 2 query or a rank order query.
    Whichever you choose, then make a new query with 2 copies of the original query.
    Join using a suitable code and calculate the difference between them
    Have a look at a similar example on my website http://www.mendipdatasystems.co.uk/g...lue/4594484854

    OR possibly use a subquery http://allenbrowne.com/subquery-01.html
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

  3. #3
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    120
    Quote Originally Posted by ridders52 View Post
    Several methods e.g.
    Create a SELECT TOP 2 query or a rank order query.
    Whichever you choose, then make a new query with 2 copies of the original query.
    Join using a suitable code and calculate the difference between them
    Have a look at a similar example on my website http://www.mendipdatasystems.co.uk/g...lue/4594484854

    OR possibly use a subquery http://allenbrowne.com/subquery-01.html
    Thanks. I'll give that a try.

    Wayne

  4. #4
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,708
    OK - good luck. It shouldn't be too hard
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

  5. #5
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    120
    Quote Originally Posted by ridders52 View Post
    OK - good luck. It shouldn't be too hard
    It worked!

    Thanks. I'll be able to use that technique for other applications also.

  6. #6
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,708
    Excellent. Which method did you use?
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

  7. #7
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    120
    The first one. :-)

  8. #8
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,708
    As you say, the method is easily transferrable to other situations but the other two methods are more versatile still as they aren't limited to two records.
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

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

Similar Threads

  1. Using MAX to show largest purchase and the date
    By rockell333 in forum Queries
    Replies: 3
    Last Post: 04-21-2016, 04:07 AM
  2. Replies: 3
    Last Post: 08-31-2015, 08:04 AM
  3. Where Condition larger than 255
    By intrepid in forum Access
    Replies: 7
    Last Post: 02-28-2015, 01:44 PM
  4. Replies: 1
    Last Post: 11-13-2012, 04:03 PM
  5. Query to find the second largest year
    By hawkins in forum Queries
    Replies: 2
    Last Post: 07-05-2011, 10:17 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
  •  
Tech Forums: Microsoft Office Forums