Creating a report using the most recent records by date

[Guest]

Hello all,

I am hoping someone can help me with a problem I am running into. I am very
new to access and have been reading through the discussions and cannot figure
this out. I am trying to create a report of various individuals that contains
a subreport of the scores they received. I only want the most recent scores
and description. I have 2 tables: tblEmployee and tblScores. The tblEmployee
contains employee info and the tblScores has six fields (ScoreID
(autonumber), employeeID, testDate, score1, score2, and description). How do
I get only the most recent testdate to appear in the subreport instead of all
the records for each employee.

Thanks,

 

[John Spencer]

Why use a subreport? You can get all the data in one query.

SELECT *
FROM TblEmployees INNER JOIN tblScores
ON tblEmployee.EmployeeID = TblScores.EmployeeID
WHERE tblScores.TestDate =
(SELECT Max(TestDate)
FROM tblScores as Temp
WHERE temp.EmployeeID = TblScores.EmployeeID)

If you are using the subreport, you need its source to be
SELECT *
FROM tblScores
WHERE tblScores.TestDate =
(SELECT Max(TestDate)
FROM tblScores as Temp
WHERE temp.EmployeeID = TblScores.EmployeeID)