Use outside query for inside

[Dave]

I'm using Access 2000 and need to create this specific query result. My
first query gives me the average for each process (1 thru n). That is simple
and already done. However the second query must pick out the lowest element
that contributed to the score of the first process.
The first sql statement is:
SELECT tblProductLines.Product_Line, Avg(tblRatios.score) AS AvgScore,
tblIndex.ProductID
FROM (tblIndex INNER JOIN tblRatios ON tblIndex.RecordID =
tblRatios.RecordID) INNER JOIN tblProductLines ON tblIndex.ProductID =
tblProductLines.ProductID
WHERE (((tblIndex.RecordDate)=#8/20/2007#))
GROUP BY tblProductLines.Product_Line, tblIndex.ProductID
ORDER BY Avg(tblRatios.score);

Within each ProductID there are various elements contributing to the score.
I need to know the #1 (min score) element for each product. So for 30
processes from the first query there should be 30 elements reported as well.
Both querys are to be used in their own graph. Monday's are tough. TIA for
any suggestions.

Dave

 

[Ofer Cohen]

Look at this link on "Subquery basics"

http://allenbrowne.com/subquery-01.html