MS Access: LEFT JOIN with Filters

Question

My MS Access version is 2003, in case that matters.

I have a single table with daily values for securities in an account. I'd like to compare the values of all securities in each account, one year ago versus today (and create an expression for the difference). The securities in the account may change over the course of a year, so there must be NULL values when linking by security. Accordingly, I'd like to perform a FULL OUTER JOIN, which I understand is not possible in MS Access. Alternatively, I'll have to create a UNION of a LEFT JOIN and RIGHT JOIN, as suggested in this SO post.

Although the below query behaves like an INNER JOIN, I believe the picture will help illustrate what I'm trying to accomplish:

I understand that creating this query in Design View causes the filters to go into the WHERE clause, which is filtering out data before the LEFT JOIN is performed. I'm attempting to replicate the solution proposed in this SO post, so far unsuccessfully. Following is my current SQL statement:

SELECT  dbo_vw_Core_Monitor_Historical.AsOFdate, 
    dbo_vw_Core_Monitor_Historical.Account, 
    dbo_vw_Core_Monitor_Historical.SecID, 
    dbo_vw_Core_Monitor_Historical.YTM, 
    dbo_vw_Core_Monitor_Historical_1.AsOFdate, 
    dbo_vw_Core_Monitor_Historical_1.Account, 
    dbo_vw_Core_Monitor_Historical_1.SecID, 
    dbo_vw_Core_Monitor_Historical_1.YTM, 
    [dbo_vw_Core_Monitor_Historical_1.YTM] - [dbo_vw_Core_Monitor_Historical.YTM] AS YTM_Change

FROM dbo_vw_Core_Monitor_Historical 

LEFT JOIN 
    dbo_vw_Core_Monitor_Historical AS dbo_vw_Core_Monitor_Historical_1 
ON  ((dbo_vw_Core_Monitor_Historical.Account = dbo_vw_Core_Monitor_Historical_1.Account) 
    AND (dbo_vw_Core_Monitor_Historical.SecID = dbo_vw_Core_Monitor_Historical_1.SecID) 
    AND ((dbo_vw_Core_Monitor_Historical_1.AsOFdate)=#12/8/2015#))

WHERE ((dbo_vw_Core_Monitor_Historical.AsOFdate)=#12/8/2014#);

I've tried a few different queries, but I believe the above is most correct based on what I've gathered from SO. This causes MS Access to immediately crash. I'm expecting output something like the below (where the highlights are for SecID's no longer in the account as of 12/8/2015:

Any advice? Is this just a symptom of using MS Access, rather than some more robust database?

Are you certain you will have the exact dates that are one year apart? If not, you will need to have a 'DateDiff' between the two nearest dates to being 1 year apart and use that in your formula(s). Possibly you need several queries - one selects current dates, the other selects 'near year back', then join the two and create your formulas? — Wayne G. Dunn, Dec 14 '15 at 18:05
@WayneG.Dunn Actually the dates I'm concerned about are variable. I just arbitrarily provided dates that are approximately 1 year apart of illustrative purposes. — ch-pub, Dec 14 '15 at 18:08
I understand the sample dates, however you mention possible 'Null' securities caused by changes in holdings. It would be best to understand now how you want to deal with a security that may not have an exact date 1 year ago (or today's date). For example if you have a security price from 363 days ago (and it's up 50%!!), do you care if it is excluded from your query? You can use an 'IF' to check for nulls and replace with "" — Wayne G. Dunn, Dec 14 '15 at 18:19
@WayneG.Dunn I'm not sure I understand your question. In this example (data from date A LEFT JOIN data from date B), I expect the output to contain all securities/values in the account as of date A. If any of those securities are no longer in the account as of date B, I expect the securities/values to be NULL as of date B. — ch-pub, Dec 14 '15 at 18:48
@WayneG.Dunn I've updated the post with an example of the expected output. — ch-pub, Dec 14 '15 at 18:58
To get the empty rows as shown, you need to change your selection criteria to something like: #3/1/2015# Or Is Null. Without creating your tables & fields, I'm not sure why Access crashes (is that really what happens, or do you just get some error?) — Wayne G. Dunn, Dec 14 '15 at 19:23
No, the LEFT JOIN inherently includes NULL values for missing records on the right side. Only if the record is there but has an empty join field you must add a `OR IS NULL`. — Olivier Jacot-Descombes, Dec 14 '15 at 19:41

score 0 · Answer 1 · answered Dec 14 '15 at 19:34

You must make a difference between records that are there but with NULL fields and records that are missing on the right (outer) side. If you are dealing with missing records, your query looks correct. I have no idea why Access crashes. You could change the query into a pass-through query. This means that the query will be executed by SQL-Server. Of course it must be written in T-SQL then:

SELECT
    A.AsOFdate,
    A.Account,
    A.SecID,
    A.YTM,
    B.AsOFdate,
    B.Account,
    B.SecID,
    B.YTM,
    B.YTM - A.YTM AS YTM_Change
FROM
    dbo.vw_Core_Monitor_Historical A
    LEFT JOIN dbo.vw_Core_Monitor_Historical AS B
        ON  A.Account = B.Account AND
            A.SecID = B.SecID AND
            B.AsOFdate = '2015/12/08'
WHERE
    A.AsOFdate = '2014/12/08';

MS Access: LEFT JOIN with Filters

1 Answers1