Nested Query, Return most recent record

[kaylachris]

Attempting to nest a query inside another query but uncertain of the correct syntax. The following table

Table: lclTempImportHW

LastName	FirstName	MiddleName	DateRecorded	BodyFatPass	BodyFatPercent	Height	Weight	HeightWeightPass

This query successfully returns the most recent date for each individual.

SELECT Max(lclTempImportHW.DateRecorded) AS MaxOfDateRecorded, lclTempImportHW.LastName, lclTempImportHW.FirstName, lclTempImportHW.MiddleName
FROM lclTempImportHW
GROUP BY lclTempImportHW.LastName, lclTempImportHW.FirstName, lclTempImportHW.MiddleName;

However, I need the rest of the columns of data to go along with these dates as well as pulling the StudentID from tbl_StudentData...

SELECT tbl_StudentData.StudentID_PK, lclTempImportHW.*
FROM tbl_StudentData INNER JOIN ((lclTempImportHW ON (tbl_StudentData.F_Name = lclTempImportHW.FirstName) AND (tbl_StudentData.M_Name = lclTempImportHW.MiddleName) AND (tbl_StudentData.L_Name = lclTempImportHW.LastName)) INNER JOIN (SELECT Max(lclTempImportHW.DateRecorded) AS MaxOfDateRecorded, lclTempImportHW.LastName, lclTempImportHW.FirstName, lclTempImportHW.MiddleName
FROM lclTempImportHW
GROUP BY lclTempImportHW.LastName, lclTempImportHW.FirstName, lclTempImportHW.MiddleName) as maxDateQry ON (maxDateQry.MaxOfDateRecorded) AND (lclTempImportHW.LastName = maxDateQry.LastName) AND (lclTempImportHW.FirstName = maxDateQry.FirstName) AND (lclTempImportHW.MiddleName = maxDateQry.MiddleName) ;

This is what I have so far but gives me a "Syntax error in JOIN operation"

[pbaldy]

A two query solution:

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

It can be done as a subquery, but is harder to maintain that way in my opinion.

[kaylachris]

That's what I had originally done. However, I have a total of 6 tables that are populated from .transferspreadsheet. Each contains different sets of data but all contain the same fields for names and DateRecorded. I need the most recent data for each person from each table. My intent is to hard code the SQL statement and inject the different table names with variables so I can pull it into a recordset and manipulate the data (i.e. trim extra spaces and normalize). After all that move it to its appropriate permanent table.

I'm sure this can all be done with one massive query but I wouldn't even know where to begin to start writing that. Furthermore, using dao I can through up a progress bar so users dont think the program crashed as it pushes through all this data.

I also considered creating a excel object.. merging all 6 tables then importing as one table. But from what I've read this would be the most cpu intensive process of them all.