Results 1 to 5 of 5
  1. #1
    binaarycode is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2019
    Posts
    7

    Another query another problem - Top scores

    I've spent the last 4 hours trying to get my head around TOP N - I've no knowledge of SQL and I'm trying to grab the top score from each Software ID out of a table.


    Code:
    SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
    FROM Orders
    WHERE Orders.OrderID IN
       (SELECT TOP 3 OrderID                            
       FROM Orders AS Dupe                              
       WHERE Dupe.CustomerID = Orders.CustomerID        
       ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC) 
    ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID;
    The above code is from allenbrowne and the code below is currently what I'm playing with.



    Code:
    SELECT [Member Game].[Member Reference], [Member Game].[Software Reference], [Member Game].Score
    FROM [Member Game]
    WHERE [Member Game].[Software Reference] IN
     (SELECT TOP 1 [Member Game].Score
    FROM [Member Game] AS Dupe
    WHERE Dupe.[Member Game].Score =  [Member Game].Score
    ORDER BY [Member Game].[Software Reference]);
    I'm still unsure about how the following bit of code works

    Code:
    (SELECT TOP 3 OrderID                            
       FROM Orders AS Dupe                              
       WHERE Dupe.CustomerID = Orders.CustomerID        
       ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC)
    Any help is appriciated.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Dupe is an alias used to differentiate the separate use of the orders table.

    What I don't understand is why you want the top score - but ordering by reference? why not order by score desc (descending, so the highest score is at the top)

  3. #3
    binaarycode is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2019
    Posts
    7
    Quote Originally Posted by Ajax View Post

    What I don't understand is why you want the top score - but ordering by reference? why not order by score desc (descending, so the highest score is at the top)
    I was just trying to tweek the allenbrowne code and hope for the best after three hours of reading different posts and youtube videos leading nowhere. The database is for a college assignment, we've not been taught SQL and limited teaching with Access too, so It's more of a try, try try again approach.

    so I should be looking at WHERE [Member Game].Score DESC;?

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you are familiar with the query design grid, then you know that each column represents a table field (OK, let's ignore fields that are being calculated for now).
    Then you also probably know that you don't add criteria in a field that doesn't belong to that field, such as trying to apply phone number values as criteria on a name field.

    A subquery is a way of getting values from a record from the same table in order to use them as criteria. As noted, because it's from the same table, the subquery must refer to that table using an alias, but that's not the most important point. Your subquery is getting MemberName.Score and using it as criteria for [Software Reference]:
    Code:
    WHERE [Member Game].[Software Reference] IN
     (SELECT TOP 1 [Member Game].Score
    Take a look at your query in design view and let me know if I'm way off the mark here - because I really struggle with subqueries, although I usually win.
    EDIT- Usually, if you need a top from the subquery, you have to use an ORDER BY in it. You only have an order in the outer query. Maybe post some data and desired results rather than sql that doesn't work. Centered text in Excel that you copy and paste makes a decent html table here and keeps data aligned better.
    Last edited by Micron; 03-16-2019 at 04:09 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you are looking for the Top Score by individual/member and what Software was that score achieved in, then this may be a solution.
    qryTopScoreByMember
    Code:
    SELECT [Member Game].[Member Reference]
        ,[Member Game].[Software Reference]
        ,[Member Game].Score
    FROM [Member Game]
    WHERE [Member Game].[Score] IN (
            SELECT TOP 1 [Dupe].Score
            FROM [Member Game] AS Dupe
            WHERE Dupe.[Member Reference] = [Member Game].[Member Reference]
            ORDER BY Dupe.[Member Reference]
                ,Dupe.score DESC
            )
    ORDER BY [Member Game].[Member Reference]
        ,[Member Game].[Software Reference]
        ,[Member Game].Score;

    If the question is to report the highest score for each Software, then the following may be a solution.

    Code:
    SELECT [Member Game].[Software Reference]
        ,Max([Member Game].Score) AS MaxOfScore
    FROM [Member Game]
    GROUP BY [Member Game].[Software Reference];

    If you need the HiScore for each Software and who scored that mark, then this may be helpful.

    qryHiScoreBySoftwareShowMember
    Code:
    SELECT [Member Game].[Software Reference]
        ,[Member Game].score AS HiScore
        ,[Member Game].[Member Reference] AS ScoredByMember
    FROM [Member Game]
    WHERE [member game].score = (
            SELECT Max([dup].[Score])
            FROM [Member Game] AS dup
            WHERE dup.[software reference] = [Member Game].[Software Reference]
            )
    ORDER BY [Member Game].[software reference];

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

Similar Threads

  1. assign a descriptor to scores from many tables
    By neuropsychresearch in forum Queries
    Replies: 2
    Last Post: 02-25-2018, 03:10 PM
  2. Trying to average two test scores using query
    By Ddempsii in forum Access
    Replies: 9
    Last Post: 11-04-2015, 02:01 PM
  3. Creating database for test scores
    By Dotson525 in forum Access
    Replies: 5
    Last Post: 06-16-2015, 07:47 AM
  4. A Report to count the scores of a review
    By endwarde in forum Reports
    Replies: 7
    Last Post: 04-18-2014, 07:00 AM
  5. Testing Scores
    By helpaccess in forum Queries
    Replies: 4
    Last Post: 08-22-2011, 09:08 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