Complex Query not showing all Results

[markcarroll]

I have a rather complicating query (the SQL is about a page long) so I hope I can solve this without needing to get into specifics.

Basically, the database I am working on has information about investment firms, and what companies they hold shares of. I set up the query to find all the stocks that a specified company held for the most recent date. But we need to display data for the past dates as well.

The way I got the most recent date to display was to make a seperate query that found the max date. I then put that query into the main query and joined that query with the 'filing date' field in the 'Holdings' table that has the share information.

I have been able to get the query to display other dates as well by putting the 'holdings' table in again (So now there is a 'holdings' 'holdings_2', 'holdings_3', etc), and joining them to their respecive dates the same way as I did the original 'holdings' table.

Everything looked like it worked fine as I could then make the query display back multiple dates. But then I realized that it only displayed information if the investment firm held stocks in that company on the most recent date (original 'holdings' table). Sometimes, an institution will have owned the stock for several quarters, but then sell it all off so that they currently own nothing. If that is the case, then the query is not displaying that stock at all. What I want would be for it to show the stock in the results, with appropriate totals for each date.

Lets say that I am looking at the shares of ABC and XYZ companies that an institution hold. If the institution held 100 shares of ABC on Date1 and 200 shares on Date2, then everything comes out correctly. However if the institution owned 200 shares of XYZ on Date2 and zero on Date1, then XYZ will not appear in the final query at all. (Had they owned XYZ on Date1 instead of Date2, then it shows up). The problem might have something to do with the 'holdings' table having no row with XYZ and Date1.

So basically, how do I make the query show results that may have null or zero values in some of the dates but have values for different dates?

[Stewart Ross]

Hi Mark. On the face of it, it sounds like you need to consider the joins between your stock holding table and your institution table and use a left-join between the stock holding and the institution at some point (a left-join includes all instances of the originating table, not just those where there is equality in the joined fields with whatever they are joined to).

However, in multi-table queries the introduction of left (or right) joins has to be done consistently; if one join is done this way all joins will have to follow suit, as Access will protest at inconsistent join types. I break down complex queries into simpler segments, using left-joins sparingly in separate queries which feed into others that are inner-joined as normal.

There is a HowTo article by our highly experienced admin contributor NeoPa on SQL Joins, linked here for reference.

It is difficult to advise further on this without seeing the SQL concerned, or knowing what the structure of the underlying tables is. I think there may be some issues relating to how the query is currently structured - very long SQL is often very difficult to read, understand and maintain, and tends to suggest structural difficulties in the underlying tables or what is being attempted.

-Stewart

[markcarroll]

Thanks for the help. I have been able to make a lot of progress with the LEFT JOIN feature. However, I run into a problem.

When I use the LEFT JOIN, I end up having a whole bunch of rows with no information in them on the report. So I add a where clause with: sharesheld is not null or sharesheld2 is not null etc. This makes it actually display the correct results, but it takes a LONG time to run the query. Here is my SQL:

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_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));

What I want to do is add the "Is Not Null" into the LEFT JOIN statement. But if I do that, access creates an error. Apparnelty, this is just an access limitation. Is there any way to make a work around?

IE I want

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

For each of the LEFT JOIN statements.

The only thing I can think of is somehow putting the query onto SQL server. The problem is that the queries on the right side need to be in access as I use criteria in them based on Access Reports. So any other help would be much appreciated.