Sub-table Query

[Kilau via AccessMonster.com]

I have a main table with Names, IdNumber, TestDate and TestScore. Since We
have to retest every month, I have another table with IdNumber, ReTestDate,
ReTestScore. I have the IdNumber linked between the two tables.

I need a query have will pull the all the Name, TestDate, TestScore AND the
most recent ReTestDate and ReTestScore for each name.

Thankful for any help

 

[John Spencer (MVP)]

SELECT M.[Names], M.TestDate, M.TestScore, R.RetestDate, R.RetestScore
FROM MainTable as M LEFT JOIN RetestTable as R
ON M.IDnumber = R.IdNumber
WHERE R.RetestDate IN
(SELECT MAX(RetestDate)
FROM RetestTable as Temp
WHERE Temp.IDNumber = R.IDNumber)
OR 0 = (SELECT Count(RetestDate)
FROM RetestTable as Temp
WHERE Temp.IDNumber = R.IDNumber)

IF you have a retest date for EVERY idNumber then you can change the above to

 

[John Spencer (MVP)]

WHOOPS! Hit the wrong key.

IF you have a retest date for EVERY idNumber then you can change the query to:

SELECT M.[Names], M.TestDate, M.TestScore, R.RetestDate, R.RetestScore
FROM MainTable as M INNER JOIN RetestTable as R
ON M.IDnumber = R.IdNumber
WHERE R.RetestDate IN
(SELECT MAX(RetestDate)
FROM RetestTable as Temp
WHERE Temp.IDNumber = R.IDNumber)

SELECT M.[Names], M.TestDate, M.TestScore, R.RetestDate, R.RetestScore
FROM MainTable as M LEFT JOIN RetestTable as R
ON M.IDnumber = R.IdNumber
WHERE R.RetestDate IN
(SELECT MAX(RetestDate)
FROM RetestTable as Temp
WHERE Temp.IDNumber = R.IDNumber)
OR 0 = (SELECT Count(RetestDate)
FROM RetestTable as Temp
WHERE Temp.IDNumber = R.IDNumber)

IF you have a retest date for EVERY idNumber then you can change the above to

I have a main table with Names, IdNumber, TestDate and TestScore. Since We
have to retest every month, I have another table with IdNumber, ReTestDate,
ReTestScore. I have the IdNumber linked between the two tables.

I need a query have will pull the all the Name, TestDate, TestScore AND the
most recent ReTestDate and ReTestScore for each name.

Thankful for any help