title: MS Access: match XRate to date
body: <p>I have three tables: Sales, Currency, Exchange Rate</p>
  
  <p>I need the appropriate exchange rate for every sale and foreign currency transaction to be calculated to our local currency. The Exchange Rate Table has values for each foreign currency, but only for one date in each month, the last day.</p>
  
  <p>I have attempted the following thus far, which works, if the sales date happens to be exactely the last day of a month:</p>
  
  <pre><code>SELECT 
      qry_SALES.Payment_ID, 
      qry_SALES.Product_Name, 
      qry_SALES.Sales, 
      qry_SALES.SalesDate, 
      qry_SALES.SalesCcy, 
      tbl_XRate.XRate_CHF, 
      tbl_XRate.XDate
  FROM 
      qry_SALES INNER JOIN 
      (tbl_Currency INNER JOIN tbl_XRate ON tbl_Currency.Currency_ID = tbl_XRate.Currency_ID) 
      ON (qry_SALES.SalesDate = tbl_XRate.XDate) AND (qry_SALES.SalesCcy = tbl_Currency.Ccy)
  ORDER BY qry_SALES.SalesDate;
  </code></pre>
  
  <p><strong>How do I get transactions that are during a month to match up with the exchange rate table's last value?</strong></p>