Results 1 to 3 of 3
  1. #1
    zx3 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    47

    Question Get the earliest row by date for each group of names where point is <=3



    name date point race
    c 1 1 A3
    a 3 2 A3
    b 1 6 A1
    a 1 7 A1
    b 2 7 A1
    c 2 9 A1
    a 2 3 A3
    b 3 4 A2


    Hi All, the above is the table, what i need is for each group of name a, b, and c, i want only 1 row to show the earliest date on which 3 points or under is scored

    So the resulting table should be as following

    name date point race
    a 2 3 A3
    b 3 4 A2
    c 1 1 A3

    in the above table, group a and c have normal result as expected, but for b it has no match of points 3 or under, so i want the last row of the group by date(i.e. latest) to show, please help

  2. #2
    zx3 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    47
    Hi all,
    finally, i got the solution myself, and here is the file 1FmEachGrpWmptMax.accdb

    the way i deal with it is i break down the problem small queries, and these small queries basically divided into 2 legs
    to begin, i start with mystart query, give ranks to each row of each group, group them by nam
    1st leg, i called the queries as the AA series, its function is to get 1 row from each group where point <=3 is never found

    based on mystart, i build AA1 which only show row have pt >=4 in each group
    then based on AA1, i build AA2 which show only the latest row of AA1, at this point, i got only 1 row from each group which is its last row

    2nd leg, i called the queries as the BB series, its function is to get 1 row from each group where point <=3 is found
    based on mystart, i build BB1 which only show rows have pt <=3 in each group
    then based on BB1, i build BB2 which show only the rows have the min rank, at this point, i got only 1 row from each group that match the (<=3) criterion

    at last, i combine the 2 legs by 1FmGrpWhereMptIsMax query which choose only the max row of mpt from each group.

    i think there might be improvements on these 2 series of queries, and i tried to combine all queries into 1 complex query but Access just hang and crash as i try.

    i will open this thread for the coming week, for you guys to comment or to make suggestions on improvements, hopefully we can learn a bit more together, after that i will mark it as solved.

    Thanks for the attention to this matter from the thread start.

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Thanks for posting back with your success and solution.
    I marked this thread as *Solved* for you using the *Thread Tools* at the top of the thread.
    https://www.accessforums.net/showthread.php?t=1828

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

Similar Threads

  1. Replies: 4
    Last Post: 02-25-2016, 08:18 AM
  2. select the 2 earliest date records for each id
    By lbrannon in forum Queries
    Replies: 10
    Last Post: 07-14-2015, 04:10 PM
  3. Replies: 3
    Last Post: 04-19-2013, 12:49 PM
  4. Default earliest date and latest
    By Compufreak in forum Access
    Replies: 3
    Last Post: 01-10-2013, 07:17 AM
  5. Selecting Earliest and latest date
    By kstyles in forum Queries
    Replies: 10
    Last Post: 12-31-2010, 03:04 PM

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