Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    40

    Unanswered: Query...How To Return Highest Score?

    I have attached a small sample database which records the scores for every three dart throw (total) by three different dart players. The simple query returns only two fields, they are "Name" & "ThreeDartScore". When I run my query it returns 9 records in total with multiple entries for each of the three players. For example:
    Fred - 120
    Fred - 95
    Fred - 100
    Bob - 80
    Bob - 65
    Bob - 100
    Harry - 120
    Harry - 100
    Harry - 140
    Can anyone show me how to adjust the query so that it returns only three records, one for each individual player, ideally that would be the highest score achieved by each. For example:
    Fred - 120
    Bob - 100
    Harry - 140
    I can do this manualy by sorting and reporting but naturally I would prefer to have a query do it for me automatically instead. If anyone can assist I would greatly appreciate it.
    Many thanks in advance.
    Ronald A. Dixon
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    SELECT s.PlayerName, Max(s.ThreeDartScore)
    FROM Scores s
    GROUP BY s.playerName
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Aug 2004
    Posts
    40
    Many thanks Robert, but I don't know what to do with your instructions. Do I put them in a query? Do I need to make a new field? etc. Sorry, I am a novice. Would it be possible for you to "tweak" my earlier attachment and send it back to me please?
    Regards,
    Ronald A. Dixon

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Hi,

    To manually type the SQL for a query:

    Design View => View => SQL
    Attached Files Attached Files
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Aug 2004
    Posts
    40
    Perfect...everything works fine. Many thanks Robert, your kind help is very much appreciated.
    Regards,
    Ronald A. Dixon

Posting Permissions

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