Sort By Date

[John]

Hi,
My qery based report shows a list of students exam and their progress.
Each student has many exams taken with a date for each.
However I do not want all exams shown, only the last one (NewestDate)
At the moment, if I set the date to descending order in the query and show
only 1 record in the top values, all the students get the same date.
(TheNewest).

Any ideas ?

Regards

John

 

[Ken Snell \(MVP\)]

This query should give you an idea of how to set up your query for your
report:

SELECT *
FROM TableName
WHERE ExamDate =
(SELECT Max(T.ExamDate)
FROM TableName AS T
WHERE T.StudentID = TableName.StudentID);

 

[John]

Ken,
Sorry but I appear to be lost.
I am a novice user and your SQL has confused me.
Do ineed to do this in SQL mode or can I do this in query design mode?

Sorry for my ignorance.

Regards

John

 

[Rick Brandt]

Ken,
Sorry but I appear to be lost.
I am a novice user and your SQL has confused me.
Do ineed to do this in SQL mode or can I do this in query design mode?

Sorry for my ignorance.

Paste it into SQL view, adjust the table and field names to match yours, then
switch to the query designer.

(we can't post query designer stuff in a newsgroup)

 

[John]

Rick,
That works ok but I have another problem...
I think I messed up with my last post and my requirements.

The new query now shows me only 1 student and the last exam date. Correct?
However I want all students shown with their last exam date.
Any further Ideas?


Sorry for the confusion.

Regards

John

 

[John Spencer]

Two query solution.
First Query
Select your table
-- Select StudentId and Exam Date
-- Select View: Totals from the menu
-- Change GRoup By to Max under the exam date field
-- Save this query as qLastExamDate

Second query uses the above saved query and your table of exams Join the
table and the query on the studentId and the examdates and show the fields
you want.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John]

John
Many Thanks.

Guys...You are all doing a great job

Kindest Regards

John