title: Use query result as parameter for another query
body: <p>I have two tables:</p>
  
  <p>table1: item (itemId,itemName)</p>
  
  <p>table2: invoices (InvoiceId,InvoiceNum,InvoiceDate,ItemId..etc) (other columns are not relevant).</p>
  
  <p>I need to find for a distinct item, the latest (and only the latest) invoice date.
  So far I create a first query distinct on the first table and then, for each result I run a new query on the second table (top 1 ordered by date desc)</p>
  
  <p>It works, but it takes forever (I have more than 10000 items).
  I am using asp classic and access but I am pretty sure that the problem is related to the SQL.</p>
  
  <p>I tried with the clause <code>WHERE ... IN</code>  but the query output all the invoice dates and not only the latest one.</p>
  
  <p>Any help?
  Thx in advance!</p>
  
  <p>I understand that I've been very generic.
  here is the SQL instruction that I am trying to use:</p>
  
  <p>SELECT table1.id, table3.name, table1.name, table2.trans_num, Max(table2.transaction_date_h) AS MaxDitransaction_date_h, table2.amount_amt
  FROM table3 INNER JOIN (table1 INNER JOIN table2 ON table1.id = table2.item_id) ON table3.id = table1.preferred_vendor_id
  GROUP BY table1.id,table3.name, table1.name, table2.trans_num, table2.amount_amt
  ORDER BY table3.name, table1.name, table2.trans_num, Max(table2.transaction_date_h) DESC;</p>
  
  <p>If I run this query, the result is the entire recordset.
  As said, I would like to retrieve only the top 1 latest transaction_date_h for each distinct table1.id</p>