Multi-table query for 'top' value

  • Thread starter Thread starter JeffC
  • Start date Start date
J

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
 
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)
 
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)
JeffC said:
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
Click to expand...


.
Click to expand...
 
Back
Top