Results 1 to 2 of 2

Thread: Help with Query

  1. #1
    Join Date
    Jan 2007

    Unanswered: Help with Query


    Excuse my lack of knowledge, I'm pretty new at this! Hopefully someone can help me!

    I currently have a query in access which is pulled from a central database consisting of Name, Value 1 (test result), Value 2 (max possible mark). Dividing value 1 by value 2 (*100) gives me a % figure (i'm calling it Value 3!) but it's a live system and that data isn't already there in the query. Each person (name) has about 5 records each for example if i run the query it might say:

    Brian 2 20
    Brian 4 20
    Brian 50 100
    Joan 99 100
    Joan 13 20
    Simon 10 10
    Simon 15 20
    Simon 12 60

    I ultimately want a report to give each name (once), followed by their highest test result as a percentage. So how do I create that imaginary 'Value 3' column? Or is there a way to write it into the report? (and then I want to sort it highest to lowest too!)

    Name Highest Mark

    Simon 100%
    Joan 99%
    Brian 50%


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    select name, max(100.0*value1/value2) as highest_mark
      from daTable
    group by name
    order by highest_mark desc | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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