Results 1 to 4 of 4
  1. #1
    Bobby100 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    13

    Query to show current league position by selecting highest value from games_played column?

    Hi,



    I have a football database with a table for league_standings.

    I have structured this table as a list so that I update the league position after each new game played.

    The columns are TEAM, GAMES_PLAYED, POSITION.

    So for instance each team will have numerous rows in this table, one for each game they have played and their current position in the league.

    I want to build a query from this table to show the current league position.

    So I want to show the rows for each team where there entry in the 'GAMES_PLAYED' column is at its highest.

    Currently each team has entries for GAMES_PLAYED as 1 and 2 as there have only been 2 games played in the Premier League so far this season by any one team.

    How do I display the current league position of each team from this table?

    Do I need to use dmax function? I did try this but it returned an error?

    Thanks

    Rob.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    One way is do this in a series of two queries.

    First Query
    - Add the TEAM and GAMES_PLAYED fields to the query
    - Click on the Totals icon (looks like a Sigma). This will add a Totals row with the phrase "Group By" under each field
    - Change the Totals row value for the GAMES_PLAYED field from "Group By" to "Max"
    So this will return the highest (max) GAMES_PLAYED value for each team

    Second Query
    - Join your first query to your original table on both the TEAM and GAMES_PLAYED fields
    - Return all three fields from the original table
    This should return what you are looking for

  3. #3
    Bobby100 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    13
    Thanks Joe,

    I will try this out later today.

    I did have the first part as you suggested with the max values displayed via the totals button, just wasn't sure where to go from there.

    Once joining the query to the table, if I look at the underlying SQL, would this be a sub-query/ nested query?

    Thanks,

    Rob.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Once joining the query to the table, if I look at the underlying SQL, would this be a sub-query/ nested query?
    No - you are referencing a query object in the same way as a table whereas sub-queries/ nested queries are all within your main query. To convert to a nested query, copy all the sql from your first query without the semi colon at the end then in second query add in with brackets

    e.g. in its simplest form

    SELECT *
    FROM maintable inner join qry1 ON....

    becomes

    SELECT *
    FROM maintable inner join (paste sql here) AS qry1 ON....


    Note that sub queries need to have tables aliased if they appear more than once in the overall query in more than one context/instance. In this case it appears twice (once as the table and once in the nested query) but because it is a nested query, does not need to be aliased.

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

Similar Threads

  1. Selecting highest value across several fields
    By megabrown in forum Queries
    Replies: 19
    Last Post: 10-28-2014, 01:16 AM
  2. Replies: 10
    Last Post: 12-29-2013, 03:26 PM
  3. Replies: 1
    Last Post: 12-09-2013, 05:57 PM
  4. Replies: 3
    Last Post: 04-05-2012, 08:33 AM
  5. Replies: 5
    Last Post: 09-10-2010, 10:07 AM

Tags for this Thread

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