• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

I need help with Access Function

I have a simple table called Table:

ID      Code      Status                               StatusCode      EndDate
1      A              Input                               1                      9/10/2013
2      A              Verified                               2                      9/11/2013
3      A              Checked and approved       3                      9/12/2013
4      A              Closed                               4                      9/16/2013
5      B              Input                               1                      9/11/2013
6      B              Verified                               2                      9/12/2013
7      B              Checked and approved       3                      9/17/2013
8      C              Input                               1                     9/11/2013
9      C              Verified                               2                     9/12/2013
10      C              Checked and approved       3                     9/15/2013
11      C              Closed                               4                     9/16/2013

I want to retrieve only the last EndDate grouped by Code:
I was trying to use Max function,  and it does work only for Code and EndDate, if i add the rest of the fields it returned all rows of the data
so it should be:

4      A              Closed                               4                      9/16/2013
7      B              Checked and approved       3                      9/17/2013
11      C              Closed                               4                      9/16/2013
2 Solutions
Something similar to this will work:
SELECT A.ID, A.Code, A.Status, A.StatusCode, A.EndDate
(SELECT StatusCode, MAX(EndDate) AS _maxEndDate FROM Table Group BY StatusCode) B
ON A.StatusCode = B.StatusCode AND A.EndDate = B._maxEndDate

Open in new window

You can also use Top:
SELECT Table.ID, Table.Code, Table.Status, Table.StatusCode, Table.EndDate
FROM [Table]
WHERE (((Table.ID) In (Select TOP 1 T.ID FROM [Table] as T Where (T.Code = [Table].[Code]) Order By T.EndDate Desc)));

Open in new window

rfedorovAuthor Commented:
Thank you very much
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now