Monday, January 4, 2010

Performing Outer Joins on a SharePoint List from Access

I'm currently creating reports from the custom SharePoint lists I have been working on over the past few months.  The way I'm accessing the data in my SharePoint lists is through Access 2007, and then I have an Excel 2007 spreadsheet which uses the Access database to get the data. In this manner, I can use the power of Access' database engine to perform queries against my lists, and then just import the data to Excel.  It sounds cumbersome, and perhaps I'm showing some of my ignorance of Access and Excel development, but it runs much faster than the earlier version of the report which uses VBA only to generate similar reports.

That being said, I ran into an issue with one query.  I have a list which tracks FCC filings.  There can be a major filing and a minor filing, both tied to the same physical location.  So there can be one or two filings (items) per location stored in this list.  I needed to report on whether the filings were "complete".  They are considered complete if the major filing is complete, or if the minor filing is complete in the case where a location does not have a major filing.

My SQL statement was structured like this:

SELECT * FROM
(
    SELECT <<completed major filings>>
    FROM Filings
    WHERE Filings.Type = 'Major'
UNION
    SELECT <<completed minor filings>>
    FROM Filings Minor
    LEFT JOIN Filings Major ON Minor.Location = Major.Location
    WHERE Minor.Type = 'Minor'
    AND Major.Type = 'Major'
)

However, this didn't work.  The second part of the query kept behaving like an inner join.  I did some proofs-of-concept to discover that, yes, outer joins do work in Access on actual tables within Access, but they just weren't working on linked tables where the source is SharePoint.

What to do?

After beating my head against the wall for a while, and possibly pulling some hair out, I finally had the bright idea to break things into smaller chunks.  Thinking that Access had a problem joining a linked table with itself, I created an Access query which only returned items representing minor filings, and another Access query which only returned items representing major filings.  Then I removed the where clauses from my original SQL statement above and replaced direct access to the linked table with the queries I had created.

It worked beautifully!

The new SQL Statement looks like this:

SELECT * FROM
(
    SELECT <<completed major filings>>
    FROM AllMajorFilings
UNION
    SELECT <<completed minor filings>>
    FROM AllMinorFilings Minor
    LEFT JOIN AllMajorFilings Major ON Minor.Location = Major.Location
)

Lesson learned: when working with Access, break things into smaller components to avoid confusing Access.

I can't wait to get back to "normal" C# development!

No comments:

Post a Comment