MS Access: match XRate to date

Question

I have three tables: Sales, Currency, Exchange Rate

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.

I have attempted the following thus far, which works, if the sales date happens to be exactely the last day of a month:

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;

How do I get transactions that are during a month to match up with the exchange rate table's last value?

Gustav Gustav · Accepted Answer · 2019-11-11T10:17:38

Try this using the matching ultimo date of the month:

FROM 
    qry_SALES 
INNER JOIN 
    (tbl_Currency 
INNER JOIN tbl_XRate 
    ON tbl_Currency.Currency_ID = tbl_XRate.Currency_ID) 
    ON (DateSerial(Year(qry_SALES.SalesDate), Month(qry_SALES.SalesDate) + 1, 0) = tbl_XRate.XDate) 
        AND (qry_SALES.SalesCcy = tbl_Currency.Ccy)

or:

FROM 
    qry_SALES 
INNER JOIN 
    (tbl_Currency 
INNER JOIN tbl_XRate 
    ON tbl_Currency.Currency_ID = tbl_XRate.Currency_ID) 
    ON (qry_SALES.SalesCcy = tbl_Currency.Ccy)
WHERE
    DateSerial(Year(qry_SALES.SalesDate), Month(qry_SALES.SalesDate) + 1, 0) = tbl_XRate.XDate

MS Access: match XRate to date

1 Answers