Student/lecture latest query,

Lectures presented by all the students during the last year has been stored in a talbe named: studentlectures, with these fields :
ID, studentname, lecturesubject, lecturedate
For example:
1, john, mathematics, 2/3/2012
2, jack, chemistry, 10/3/2012 ,
3,john, engineering, 15/3/2012
4,jack ,chemisty,20/3/2012
5,john,politics,30/3/2012
6,john,engineering, 15/4/2012
7,jack,politics,15/4/2012
We want a query to show the latest record for each student with a specific lecture. For the above example the result will be:
1, john, mathematics, 2/3/2012
4, jack , chemisty,20/3/2012
5,john, politics,30/3/2012


6, john, engineering, 15/4/2012
7, jack,politics,15/4/2012
Previous subject records by each student are not shown, and only the latest one is shown.
Thank you

I recommend you review sql, aggregate functions and Group BY statement
http://www.w3schools.com/sql/sql_groupby.asp

can anyone help on this please?

What have you tried that isn't working, since you read the information in the link provided ?

Cross posted at https://www.access-programmers.co.uk...96#post1588596

Since this thread started in April 2013, I have to wonder Why did you come back to it 5 years later still seeking an answer?

If my table had only three fields groupby would finelywork. In fact the real table has other fields, such as professorname,audiancenumbers, roomname, and else. If i make others "groupby" aswell, i dont get only the last reacord for a given student/lecture.
After all, i couldn’t find any example on that page similarto what i am looking for.

The simple way to achieve this - Perform the grouping you need to get just the record ID's you need. Save this query as qryGrouped . Now get your original table create a new query with all the fields you need, add qryGrouped and link the two by ID.

There are more elegant ways of doing this but this will give you a starting point.

Here is some sql that may help

Code:

SELECT A.id
    ,A.studentname
    ,A.lecturesubject
    ,A.lecturedate
FROM arian AS A
WHERE (
        (
            (A.lecturedate) IN (
                SELECT TOP 1 b.lectureDate
                FROM arian AS B
                WHERE b.lecturesubject = a.lecturesubject
                    AND b.studentname = a.Studentname
                ORDER BY b.lecturedate DESC
                )
            )
        )
GROUP BY  A.id
    ,A.studentname
    ,A.lecturesubject
    ,A.lecturedate
ORDER BY A.lecturedate ASC
    ,A.studentname
    ,A.lecturesubject;

With this result

Code:

id
studentname
lecturesubject
lecturedate

1
john
mathematics
02-Mar-12

2
jack
chemistry
10-Mar-12

4
jack
chemisty
20-Mar-12

5
john
politics
30-Mar-12

7
jack
politics
15-Apr-12

6
john
engineering
15-Apr-12

NOTE: You have 2 spellings for Chemistry (and Chemisty)

When I correct the spelling, I get this result with the query identified above

Code:

id
studentname
lecturesubject
lecturedate

1
john
mathematics
02-Mar-12

4
jack
chemistry
20-Mar-12

5
john
politics
30-Mar-12

7
jack
politics
15-Apr-12

6
john
engineering
15-Apr-12

Good luck with your project

It worked. thank you very much

You are welcome. Can you tell us why there is a 5 yr gap between posts?