Re: [MS_AccessPros] Filtering Dates

 

Phucon-

It helps to think of this one step at a time.  First, you need to find the latest entry for each person:

SELECT ID, [Name], Class, DateTaken

FROM MyTable

WHERE DateTaken = 

(SELECT Max(DateTaken) FROM MyTable As T2

 WHERE T2.ID = MyTable.ID)

Now that you know the latest one, you need to find the one earlier than that.

SELECT ID, [Name], Class, DateTaken

FROM MyTable

WHERE DateTaken = 

(SELECT Max(DateTaken) FROM MyTable As T3 

WHERE T3.ID = MyTable.ID 

 AND DateTaken < 

  (SELECT Max(DateTaken) FROM MyTable As T2 

   WHERE T2.ID = MyTable.ID))

You didn't give me the name of your table, so I used "MyTable" in its place.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Oct 30, 2014, at 9:54 PM, saigonf7q5@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 I have a table that contains an ID, Name, Class, Date and more fields...  The same ID and Name can be repeated more than 1, only the Date are different. For example the same staff can take different classes on different dates as the sample table that's shown below.

ID     Name      Class       DateTaken
1      John        SQL         10/30/2014
1      John        VBA         07/20/2014
1      John        Access     03/10/2014
1      John        Excel        12/15/2013
2      Mary        Word        09/10/2014
2      Mary        Excel        06/25/2014
2      Mary        Outlook    04/12/2014
3     Jane         Access     08/20/2014
3     Jane         Excel        05/15/2014
3     Jane         Word        12/15/2013
3     Jane         OneNote  09/20/2013

 

Now, Is it possible to filter itl out, only the "2nd (second) most recent" records, right before the most recent or MAX date, which are highlighted in Green?

e.g.

 

ID Name     Class     DateTaken

1 John        VBA       07/20/2014
2 Mary        Excel     06/25/2014
3 Jane        Excel     05/15/2014

 

Thanks

 

Phucon

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___