title: INNER JOIN with a large table
body: <p>I am currently using Microsoft Access 2013, and I am trying to join Table1 and Table2 together, but the problem is that Table2 is massive. Table1 is a list of part, vendor combinations with PK as part, vendor. Table2 is a table I created with the top2 most recent quotes for each part,vendor combination. All these quotes were pulled from a table with PK quote_id. I think my creation of Table2 might be the problem, because I cannot create Table2 with every part,vendor combination (i have to filter out by vendor). This is the query I used for Table2.</p>
  
  <pre><code>a.part, a.vendor, a.quote_date
  FROM quoteTable AS a 
  WHERE a.quote_date &gt; DATEADD("yyyy", -3, DATE()) AND 
  a.quote_date IN 
   (SELECT TOP 2 quote_date 
   FROM quoteTable 
   WHERE quote_date &gt; DATEADD("yyyy", -3, DATE()) AND 
   part=a.part  AND vendor=a.vendor 
   ORDER BY quote_date DESC)
  </code></pre>
  
  <p>If anyone knows a better way to select the top 2 most recent quotes from the table for each part,vendor combination, I would really appreciate it. As for the join, this works but would take too long.</p>
  
  <pre><code>SELECT *
  FROM Table1 AS a INNER JOIN Table2 AS b ON a.id = b.id
  </code></pre>
  
  <p>I am wondering if there was a way I could use the id from Table1 to filter Table2? Something like this:</p>
  
  <pre><code>SELECT * 
  FROM Table1 AS a INNER JOIN
     (SELECT * FROM Table2 WHERE id=a.id) AS b ON a.id = b.id
  </code></pre>