Multi-table query for 'top' value

[JeffC]

I have two tables joined by a column named TestCaseNum. I
want to query for the latest test run in the child table
for a given TestCaseNum. I have the following query for
the child table that does this if I manually enter the
TestCaseNum value:

SELECT top 1 *
FROM TestRuns
WHERE TestRuns.TestCaseNum = [TestCaseNum?]
ORDER BY TestRuns.[Run #] DESC ;

But I can't figure out how to work this query into a query
that includes the parent table. I want to be able to
enter a parameter for the TestCaseFamily column that would
include many TestCaseNum values, and show the latest run
for each one.

Thanks in advance,
jeffc

 

[John Viescas]

I'm only guessing at the name of the parent table:

SELECT *
FROM TestCaseParent
INNER JOIN TestRuns
ON TestCaseParent.TestCaseNum = TestRuns.TestCaseNum
WHERE TestRuns.[Run #] =
(SELECT Max([Run #])
FROM TestRuns As TR2
WHERE TR2.TestCaseNum = TestCaseParent.TestCaseNum)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[JeffC]

Thank you, John. This works perfectly.
jeff

-----Original Message-----
I'm only guessing at the name of the parent table:

SELECT *
FROM TestCaseParent
INNER JOIN TestRuns
ON TestCaseParent.TestCaseNum = TestRuns.TestCaseNum
WHERE TestRuns.[Run #] =
(SELECT Max([Run #])
FROM TestRuns As TR2
WHERE TR2.TestCaseNum = TestCaseParent.TestCaseNum)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I have two tables joined by a column named TestCaseNum. I
want to query for the latest test run in the child table
for a given TestCaseNum. I have the following query for
the child table that does this if I manually enter the
TestCaseNum value:

SELECT top 1 *
FROM TestRuns
WHERE TestRuns.TestCaseNum = [TestCaseNum?]
ORDER BY TestRuns.[Run #] DESC ;

But I can't figure out how to work this query into a query
that includes the parent table. I want to be able to
enter a parameter for the TestCaseFamily column that would
include many TestCaseNum values, and show the latest run
for each one.

Thanks in advance,
jeffc

.