Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    May 2008
    Posts
    18

    Unanswered: Return highest of 3 values

    I'm sure this is very basic and I will get laughed out and banished from the forum but I am having brain block.

    We are running a customer survey DB and they can return a grade of Excellent, Average or Poor I am trying to get the average grade for each depot I have put a count and sum in place but I now need to return the highest value to give the average grade for the branch. Is an if statement possible or best with 3 values?

    Regards

    Alan

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Run a query that includes your count filed set the query to include totals and
    select the Max option. This will show the result with the highest number
    of selections.


    or the SQL in the query would read something like this
    SELECT Max(CountofSurvey) AS MaxOfCountofSurvey
    FROM SurveyTable;

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you want the maximum value, and its just for 3 columns then Id use a compound IIF

    eg
    select my,column,list, iif(column1>=column2,iif(column1>=column3,column1, column3),iif(column2>=column3,column3,column2))) as maxvalue from mytable order by another,column,list
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    May 2008
    Posts
    18
    Cracked it with the compound if thanks guys.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    standard SQL has the GREATEST and LEAST functions which take any number of arguments...

    SELECT GREATEST(column1,column2,column3,...) FROM ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Or this if you get more columns:

    http://support.microsoft.com/default...b;en-us;209857

    Rudy, are those functions supported in Access/Jet?
    Paul

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pbaldy
    Rudy, are those functions supported in Access/Jet?
    no

    Access is the database system furthest in left field when it comes to supporting standard SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I didn't think so, but your posting them as a solution in the Access forum made me wonder if I was wrong.
    Paul

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    posting "as a solution"? no

    as an item of interest, yes

    are you saying that learning about standard sql is not worthwhile?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Learning is always worthwhile, but since it was posted in an Access forum, it could easily be assumed that you were posting a solution to the OP's problem. Perhaps prefacing it with "Though not available in Access, standard SQL has...". That way you educate without potential confusion.
    Paul

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed with pbaldy.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you access guys are so touchy

    i guess i would be too, if access was the only database i knew


    [ducks]

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    * slap *

    Lucky you ducked!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well played that man!
    A nicer way of putting what Rudy said is: this problem may be posted in the MS Access topic; however it is an SQL problem and as such, it may benefit future readers knowing there's a better solution than an icky nested Iif.

    Thanks for the tip Rudy; I've never actually looked those functions up (because, yes, I only really work with one technology that doesn't support those keywords) but will make a note and go ponder later today
    George
    Home | Blog

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yeah, I kinda wish Access supported such functions, it would sure make some things a LOT easier.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •