Syntax for Subquery

[JJman]

I'm using the make-table query below but I only want it to give me one record for each Student_ID (alias for dbo_corr.from_descr), where it has the maximum dbo_corr.entry_date value. I know I need a HAVING clause with a sub-select statement, but don't know the syntax to do this. Help!

SELECT DateDiff("m",[entry_date],Date()) AS MonthsSinceLastLetter, dbo_corr.entry_date, dbo_corr.from_descr AS Student_ID INTO tempFinalLetters
FROM dbo_corr INNER JOIN dbo_corr_type ON dbo_corr.corr_type_id = dbo_corr_type.corr_type_id
WHERE (((dbo_corr.corr_type_id)=1) AND ((Mid([from_descr],3,3))="980"))
GROUP BY DateDiff("m",[entry_date],Date()), dbo_corr.entry_date, dbo_corr.from_descr;

 

[cmmrfrds]

Make a separate query that has the student ID and the MAX entry date and then join to this query.

SELECT Student_ID, Max(dbo_corr.entry_date) as MaxEntryDate FROM dbo_corr
Group by Student_ID

save the query for example qryStudentMaxDate

 

[JJman]

Ok, I'll give this a shot. I was hoping I wouldn't have to go this route if I could avoid it, only because the table has almost 10 million rows and even optimized queries take quite a while to run, especially since this is only one of two nested queries.

I'll let you know how it goes.
Thanks again!