Results 1 to 6 of 6
  1. #1
    seibucat is offline Novice
    Windows 10 Access 2013
    Join Date
    Feb 2016
    Posts
    2

    How to return more than 1 MAX rows, using criteria from multiple fields


    Using the table below as a reference, I would like to return the latest 2 years, for each player.

    My research provided help to identify the latest one result, but not the latest two results.

    Secondly, if a player has only 1 result (Marino) I'd still like that 1 row to be returned.

    year player touchdowns
    2008 Manning 25
    2009 Manning 26
    2010 Manning 27
    2011
    Manning 28
    2012 Manning 29
    2010 Brady 40
    2011 Brady 41
    2012
    Brady 42
    2013 Brady 43
    1974 Namath 37
    1975 Namath 38
    1976 Namath 39
    1977
    Namath 40
    1978
    Namath 41
    1982
    Marino 60

    Thanks in advance!

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    rather than using MAX, you need to use TOP e.g.

    SELECT TOP 2...

    however in a single query you need to use a subquery

    Code:
    SELECT *
    FROM myTable
    WHERE touchdowns in (SELECT TOP 2 touchdowns FROM myTable AS T WHERE player=myTable.player ORDER BY touchdown DESC)

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    it requires a complex query - you don't say whether it is max year or max touchdowns and your example happens to highlight where they are both true; so will use max year but you would have to substitute syntax if the other....am calling your table name: Table1 and copying it so there is a duplicate named Table2 ....just to help keep things straight... one can also use the same table twice with an alias name but thought I would avoid that complication for the moment.... also one might note that 'Year' is a reserved term I believe so if you have the ability to alter that field name now would be a good time....

    SELECT DISTINCT Table1.year, Table1.player, Table1.touchdowns
    FROM Table1 INNER JOIN Table2 ON Table1.player = Table2.player
    WHERE (((Table1.Year) In
    (Select Top 2 Year
    From Table2
    Where Table2.Player =Table1.Player
    Order By Touchdowns DESC)));

  4. #4
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Seeing NTC's solution I realise my solution is not quite right

  5. #5
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    well it was very good air code.... the essence of the matter is there and that's what counts...... I definitely tend to give principles rather than exact code myself although on this one I decided to actually test it myself as it had been a long time, years maybe, since doing a Top X - so was rusty....

  6. #6
    seibucat is offline Novice
    Windows 10 Access 2013
    Join Date
    Feb 2016
    Posts
    2
    Thank you folks. The table example was to simplify the actual table I am using. I used NTC code as a template, and works as expected. One thing I forgot, however, that the field I am looking to get Top 2, contains duplicate values for the YearData.

    The table represents a sports league, and within a given year, there are multiple seasons. My goal is to all data from the latest 2 "YearData" whether they played 1 season or 3 seasons (which is the max amount of season per YearData)

    My table is AllPlayerData and I created a copy called AllPlayerData_1

    ID YearData Season Name Win Loss Total PTS
    73 2011 2011-2012 Joe Blow 79 71 150 9473
    74 2012 2012-2013 Joe Blow 94 71 165 11111
    75 2013 2013 Summer Joe Blow 23 39 62 2970
    76 2013 2013 Fall Joe Blow 28 40 68 3602
    77 2014 2014 Winter Joe Blow 31 43 74 5169
    78 2014 2014 Summer Joe Blow 30 26 56 3212
    79 2014 2014 Fall Joe Blow 37 32 69 3224
    80 2015 2015 Winter Joe Blow 37 32 69 3919
    81 2015 2015 Summer Joe Blow 46 29 75 4583
    82 2015 2015 Fall Joe Blow 42 28 70 4336

    For Joe Blow, my query returned
    YearData Season Name Win Loss Total PTS
    2015 2015 Winter Joe Blow 37 32 69 3919
    2015 2015 Summer Joe Blow 46 29 75 4583
    2015 2015 Fall Joe Blow 42 28 70 4336

    For this player, my desired result is to return rows where YearData is 2014 & 2015; the latest 2 years worth of data.

    My query
    SELECT DISTINCT AllPlayerData.YearData, AllPlayerData.Season, AllPlayerData.Name, AllPlayerData.Win, AllPlayerData.Loss, AllPlayerData.Total, AllPlayerData.PTS
    FROM AllPlayerData INNER JOIN AllPlayerData_1 ON AllPlayerData.Name = AllPlayerData_1.Name
    WHERE (((AllPlayerData.YearData) In
    (Select Top 2 YearData
    From AllPlayerData_1
    Where AllPlayerData.Name = AllPlayerData_1.Name
    Order By YearData DESC)));

    Thanks for all of your help!

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

Similar Threads

  1. Replies: 3
    Last Post: 09-06-2014, 01:25 PM
  2. Replies: 6
    Last Post: 01-12-2014, 03:11 AM
  3. Replies: 3
    Last Post: 06-21-2013, 09:06 AM
  4. Replies: 1
    Last Post: 10-29-2012, 08:15 AM
  5. Replies: 11
    Last Post: 09-02-2010, 01:59 PM

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