ACCESS FEED: Re: [MS_AccessPros] Find items NOT matched

Selasa, 17 Februari 2015

Re: [MS_AccessPros] Find items NOT matched

Bob-

Yup. Access is notoriously bad solving NOT IN correlated subqueries. The outer join will always be much faster. I just included it as an alternative.

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

http://www.viescas.com/

(Paris, France)

On Feb 17, 2015, at 1:44 PM, 'Bob Phillips' bob.phillips@dsl.pipex.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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;

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

http://www.viescas.com/

(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: John Viescas <johnv@msn.com>

Reply via web post

•

Reply to sender

•