title: Query table 1 (sales data) using criteria in table 2 (definitions) - WHEN NO COMMON FIELDS
body: <p>I need to create an interactive tool in SSRS or Access where users enter definitions of promotions, then system delivers sales data relevant to those criteria. </p>
  
  <p>User will choose which promotion to report and system should use associated promo definitions to deliver results.</p>
  
  <p>I tried using a prompt to select promotion, then inserting the prompted value in a subquery in Access, but it doesn't like my syntax. It looked like:</p>
  
  <pre><code>SELECT R.NumberPresented
  FROM dbo_MKT_PromoRecapMetric R
  WHERE R.DonationDate = 
          (Select P.5007Start
           FROM [Promo Definitions] P
           WHERE P.PromoName = [Forms]![Promo.Select]![Promo]
          AND R.LocationCode = '5007')
  </code></pre>
  
  <p>I've been working on this for a LONG time, and any help would be much appreciated...</p>
  
  <p>UPDATE: Thanks for the table join solution. I should have mentioned in my post that the date criteria is simplified. I actually will need to pick up a start and end date for the promotion to drive the date range for the query. I don't think I'd be able to do that with a join.</p>
  
  <p>I think I'm looking for an equivalent of an Excel lookup in sql.</p>
  
  <p>Thanks.</p>