Thanks John,
I thought the composite join was giving me grief, but I didn't know about the filter nullifying an OUTER JOIN (but I should have thought of ANDing the conditions in the join).
Your solutions worked fine on my test database. I checked both columns for blanks, and although Blanks is an option in the filter list on both, neither have blanks. But it doesn't matter, because your solution also works fine on the real tables ( the second was noticeably slower).
Thanks again.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 17 February 2015 11:52
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Find items NOT matched
Bob-
First, when you apply a filter to the right side of a LEFT JOIN or the left side of a RIGHT JOIN, you nullify the OUTER JOIN - it ends up the same as an INNER JOIN. You've applied a WHERE to both sides, so you're not going to get what you expect.
Secondly, you need to figure out why you're getting a data type mismatch. Do you have missing values in DepartureDate or missing values in DateOfRange? (I assume both are date/time.)
If you can fix the type mismatch, what you want is something like this:
SELECT Tracking.CurrencyCode, Tracking.DepartureDate
FROM Tracking LEFT JOIN ExchangeRates
ON Tracking.CurrencyCode = ExchangeRates.Currency
AND DateSerial(Year(Tracking.DepartureDate), Month(Tracking.DepartureDate), 1) =
ExchangeRates.DateOfRate
WHERE ExchangeRates.Currency IS NULL;
Or
SELECT Tracking.CurrencyCode, Tracking.DepartureDate
FROM Tracking
WHERE Tracking.CurrencyCode NOT IN
(SELECT Currency FROM ExchangeRates
WHERE ExchangeRates.DateOfRate = DateSerial(Year(Tracking.DepartureDate), Month(Tracking.DepartureDate), 1));
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Feb 17, 2015, at 11:39 AM, 'Bob Phillips' bob.phillips@dsl.pipex.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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 (4) |
Tidak ada komentar:
Posting Komentar