Query with From y LEFT JOIN x ON y.a = x.a AND x.b IS NOT NULL

[markcarroll]

Right now I have the following SQL query inside of an access database:

SELECT dbo_SecuritiesUniverse.SecurityId, dbo_SecuritiesUniverse.SecurityTicker,
[Copy Of Test_Securities_SixQuarters_Test_Date1_2].MarketValue,
[Copy Of Test_Securities_SixQuarters_Test_Date1_2].SharePrice,
[Copy Of Test_Securities_SixQuarters_Test_Date2_2].SharePrice2,
[Copy Of Test_Securities_SixQuarters_Test_Date1_2].SharesHeld,
[Copy Of Test_Securities_SixQuarters_Test_Date2_2].Held2,
[Copy Of Test_Securities_SixQuarters_Test_Date3_2].Held3,
[Copy Of Test_Securities_SixQuarters_Test_Date3_2].Held4P,
[Copy Of Test_Securities_SixQuarters_Test_Date5_2].Held5,
[Copy Of Test_Securities_SixQuarters_Test_Date5_2].Held6

FROM (((dbo_SecuritiesUniverse

LEFT JOIN [Copy Of Test_Securities_SixQuarters_Test_Date1_2] ON dbo_SecuritiesUniverse.SecurityId = [Copy Of Test_Securities_SixQuarters_Test_Date1_2].SecurityId)

LEFT JOIN [Copy Of Test_Securities_SixQuarters_Test_Date3_2] ON dbo_SecuritiesUniverse.SecurityId = [Copy Of Test_Securities_SixQuarters_Test_Date3_2].SecurityId)

LEFT JOIN [Copy Of Test_Securities_SixQuarters_Test_Date5_2] ON dbo_SecuritiesUniverse.SecurityId = [Copy Of Test_Securities_SixQuarters_Test_Date5_2].SecurityId)

LEFT JOIN [Copy Of Test_Securities_SixQuarters_Test_Date2_2] ON dbo_SecuritiesUniverse.SecurityId = [Copy Of Test_Securities_SixQuarters_Test_Date2_2].SecurityId

WHERE ((([Copy Of Test_Securities_SixQuarters_Test_Date1_2].SharesHeld) Is Not Null)) OR ((([Copy Of Test_Securities_SixQuarters_Test_Date2_2].Held2) Is Not Null)) OR ((([Copy Of Test_Securities_SixQuarters_Test_Date3_2].Held3) Is Not Null)) OR ((([Copy Of Test_Securities_SixQuarters_Test_Date3_2].Held4P) Is Not Null)) OR ((([Copy Of Test_Securities_SixQuarters_Test_Date5_2].Held5) Is Not Null)) OR ((([Copy Of Test_Securities_SixQuarters_Test_Date5_2].Held6) Is Not Null));

It works, but it runs VERY slowly. I figure I could speed it up if I could inculde the WHERE conditions as part of of the LEFT JOIN:

LEFT JOIN [Copy Of Test_Securities_SixQuarters_Test_Date1_2] ON dbo_SecuritiesUniverse.SecurityId = [Copy Of Test_Securities_SixQuarters_Test_Date1_2].SecurityId AND [Copy Of Test_Securities_SixQuarters_Test_Date1_2].SharesHeld Is Not Null

Access gives me an error why I try this. Apparently, the Join expression is not supported. I was wondering if there was some work around in Access?

Thanks for any help.

[Stewart Ross]

Hi Mark. It would be appreciated if you don't double-post your questions (see last post you made in thread http://bytes.com/forum/thread793373.html).

I think the speed issue may relate more to the complexity of the queries you are joining than it does to the use of OR'd Is Null criteria. Without knowing the details of the tables involved it is difficult to be specific, but the tests for null are not normally slow in performance.

You have a very long name for your underlying query, which may contribute a little to a slowdown in performance (as parsing each field name involves interpreting the long qualified query name). I think this is not significant when compared to what may well be complex underlying queries calculating the stock holdings etc.

-Stewart

[NeoPa]

INNER JOIN Operation
Combines records from two tables whenever there are matching values in a common field.

Syntax
FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2

The INNER JOIN operation has these parts:

Part            Description 
table1, table2  The names of the tables from which records are combined. 
field1, field2  The names of the fields that are joined. If they are not numeric, the fields must be of the same data type and contain the same kind of data, but they do not have to have the same name. 
compopr         Any relational comparison operator: "=," "<," ">," "<=," ">=," or "<>."

I suspect that " AND table1.field1 IS NOT NULL" will probably work within the FROM clause, even though not strictly within the specified list of compoprs.

[markcarroll]

Sorry about the double post. I tried to see if I could edit out the question there but I do not see how.

I tried changing the query names. There is not a noticeable improvement. If I take the Where is not nulls out, the report still comes up faster - even though it goes from a 3 page report to a 6000 page report (formatting does take longer).

I actually found a forum where the person had a similar problem to me when they had a query structured exactly the same. They were on SQL Server and the solution was to move the not is null into the from statement with Ands. (They said it improved performance from 42 minutes to 20 seconds).

My report can take that long to run if there are a lot of people in it. So I tried to do the same AND in the FROM statement (as seen on first post). Access will not even let me save the query once I do. It brings up a message box saying:

JOIN expression not supported.

So apparently it is just an Access limitation. So if anyone knows of some kind of workaround, it would be very helpful.

The only thing I could think of was to somehow put the query on the SQL server backend (I have no experience with SQL Server though). If I do that, will I still be able to link to the sub queries in the access database? Is it even possible?

[NeoPa]

Certainly MS SQL supports more JOIN types than Access does so this is not entirely surprising.

...
The only thing I could think of was to somehow put the query on the SQL server backend (I have no experience with SQL Server though). If I do that, will I still be able to link to the sub queries in the access database? Is it even possible?

Accessing a SQL Server (T-SQL) query can be done using a Pass-Through query in Access. Unfortunately, the Access SQL cannot be mixed up in the same object.

However, it should be possible, using a Pass-Though, to return results that are later merged with and run against Access queries and sub-queries.