Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    24

    Unanswered: Access 2003 - Query To Retrieve the Latest Record

    Hi,
    Iam trying to retrieve the last record, based on date field (empanelDate) for each counselID from the table EmpanelmentTran.
    Code:
    SELECT EmpanelmentTran.counselID, Last(EmpanelmentTran.empanelDate) AS LastOfempanelDate
    FROM EmpanelmentTran
    GROUP BY EmpanelmentTran.counselID, EmpanelmentTran.empanelmentPurposeID;
    The above code retrieves all the records not just the last record for each counselID

    PS: I tried using MAX instead of LAST but with same result...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    SELECT EmpanelmentTran.*
      FROM ( SELECT EmpanelmentTran.counselID
                  , MAX(EmpanelmentTran.empanelDate) AS LastOfempanelDate
               FROM EmpanelmentTran
             GROUP 
                 BY EmpanelmentTran.counselID ) AS m
    INNER
      JOIN EmpanelmentTran
        ON EmpanelmentTran.counselID = m.counselID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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