My RIGHT OUTER JOIN is actually LEFT OUTER JOIN. Apologies for that.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 17 February 2015 10:27
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Find items NOT matched
I have two tables,Tracking which has fact details including a currency code and transaction date, and ExchangeRates which has the currency code and the exchange rate on the first day of each month.
Many reports return no results if there is no exchange rate for a month, so I want a simple query of those currencies that have Tracking items but do NOT have a corresponding rate for that transaction date.
There are many instances of each currency for the various exchange rate dates. I can get a list of unique currency/rate date pairs with
SELECT DISTINCT trk.CurrencyCode, DateSerial(Year(trk.DepartureDate),Month(trk.DepartureDate) ,1)
FROM Tracking AS trk
but when I try and join to ExchangeRates as below I get a data type mismatch error (If I create a simplified set of those tables in a new database, the query runs but only shows where tracking has corresponding ExchangeRate records).
SELECT DISTINCT trk.CurrencyCode, DateSerial(Year(trk.DepartureDate),Month(trk.DepartureDate),1)
FROM Tracking AS trk
RIGHT OUTER JOIN ExchangeRates AS xr ON trk.CurrencyCode = xr.Currency
WHERE xr.DateOfRate= DateSerial(Year(trk.DepartureDate),Month(trk.DepartureDate),1)
ORDER BY DateSerial(Year(trk.DepartureDate),Month(trk.DepartureDate),1), trk.CurrencyCode;
How do I correct this, and then invert it to show the items that have no corresponding item in ExchangeRates?
Posted by: "Bob Phillips" <bob.phillips@dsl.pipex.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar