Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Location
    Havre de Grace, Maryland, USA
    Posts
    8

    Red face Unanswered: Finding last records

    OK - I'm just dumb today!

    I have a table "tblMembers" with a Member_ID (11,000 records)

    I have a table "tblRevenues" where a one-to-many relationship with "tblMembers" exists.

    I need a query to find the last amount all members subscribed.

    I can't, for the life of me, figure this one out in an access query!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select xx.Member_ID, RevDate, RevAmount
    from tblMembers xx
    inner join tblRevenues
    on xx.Member_ID = tblRevenues.Member_ID
    where RevDate =
    ( select max(RevDate)
    from tblRevenues
    where Member_ID = xx.Member_ID )

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Sep 2002
    Location
    Havre de Grace, Maryland, USA
    Posts
    8

    Red face Thank You

    Thank you, Rudy, but doesn't this just return 1 record for the selected member?
    The query I need is for all members - their last payment by date.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    to be honest, i didn't test it, but i'm pretty sure it will work

    it's a correlated subquery

    for each row in the outer query, xx, the subquery gets the last date out of all the rows which have the same member_id as the row that the outer query is looking at, the last date in the group that the outer row belongs to by virtue of its member_id

    this date is re-evaluated for every row of the outer table (that's what makes it a correlated subquery), and is used to accept or reject that particular row from the result set

    so the only rows in the result set are the rows that have the last RevDate for each member_id

    usually, this is one per member, but in practice, it could be more than one, as far as the syntax is concerned

    real-world applications usually use a datetime datatype for RevDate, so this query will return only the last revenue per member, assuming no two revenues could be made at the same time down to the second

    but if RevDate were a date datatype, not including a time, and the application allowed a member to have more than one revenue on the same day, then the query will return all rows for that last day per member

    this is not a shortcoming of this particular sql syntax structure ("select the row with the highest column value in a group") but simply a remark on the effects of choosing the correct datatype for RevDate for your purposes

    helps?

  5. #5
    Join Date
    Sep 2002
    Location
    Havre de Grace, Maryland, USA
    Posts
    8
    Thank you so much for the lead Rudy, the concept was right, but the query wasn't quite correct, however, with a bit of juggling, I made it work. In case you ever need it, here is how it should be:
    SELECT [tblRevenues].[MD_ID], [ADate], [Amount]
    FROM tblRevenues INNER JOIN tblMembers ON [tblRevenues].[MD_ID]=[tblMembers].[MD_ID]
    WHERE ADate = ( select max(ADate) from tblRevenues where MD_ID = [tblMembers].MD_ID );

    Again, Many thanks for the pointer
    John

    Originally posted by r937
    to be honest, i didn't test it, but i'm pretty sure it will work

    it's a correlated subquery

    for each row in the outer query, xx, the subquery gets the last date out of all the rows which have the same member_id as the row that the outer query is looking at, the last date in the group that the outer row belongs to by virtue of its member_id

    this date is re-evaluated for every row of the outer table (that's what makes it a correlated subquery), and is used to accept or reject that particular row from the result set

    so the only rows in the result set are the rows that have the last RevDate for each member_id

    usually, this is one per member, but in practice, it could be more than one, as far as the syntax is concerned

    real-world applications usually use a datetime datatype for RevDate, so this query will return only the last revenue per member, assuming no two revenues could be made at the same time down to the second

    but if RevDate were a date datatype, not including a time, and the application allowed a member to have more than one revenue on the same day, then the query will return all rows for that last day per member

    this is not a shortcoming of this particular sql syntax structure ("select the row with the highest column value in a group") but simply a remark on the effects of choosing the correct datatype for RevDate for your purposes

    helps?

Posting Permissions

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