Sub-table Query

  • Thread starter Thread starter Kilau via AccessMonster.com
  • Start date Start date
K

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
 
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
 
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)

John Spencer (MVP) said:
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

Kilau via AccessMonster.com said:
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
Click to expand...
Click to expand...
 
Back
Top