Help with isolating lastest date/time with MAX function or Latest or?

[ERB]

I cant attached a screen shot so I will make a real simple mock up of the problem I am trying to solve asap .



Table:

Student_ID FN LN DateTaken

1001 John Doe 2/16/2012 4:17:00 PM

1001 John Doe 2/17/2012 4:18:00 PM

1002 Jane Doe 1/18/2012 11:30:00 AM

1002 Jane Doe 1/18/2012 2:30:00 PM


My dilema is that I have student records with either mulptiple test dates or or test taken times. What I would like to do, is to reduce this down so that I only end up with those student records that have the lastest test date or time taken. I have tried the MAX and latest functions, is there a way to go about this that I am not seeing? I am using Access 2007 Below is an example of what I would like the above to look like.


1001 John Doe 2/17/2012 4:18:00 PM

1002 Jane Doe 1/18/2012 2:30:00 PM


Thank you very much for any help or clues you guys can provide.

[alansidman]

You will need an aggregate query. Look at this page and scroll down to Group By features.

http://www.techonthenet.com/access/queries/index.php

You will have to aggregate on the Student ID and if you need the name, then join it in a subsequent query with the Student Table.

[pbaldy]

Does this help?

http://www.baldyweb.com/LastValue.htm

[ERB]

Wow thank you both very much. It was a big help! I finally got it to work. I am still testing to be sure, but it looks like it will do what I need it to. Saved me a lot of time and frustration. Thanks again!

[ERB]

how do I make this thread as solved? or is that done by the moderator?

[pbaldy]

Happy to help. You can mark it solved, via the "Thread Tools" link at the top of the thread.