Xeny is not fond of Access.

June 14, 2006 2:37 PM

Dealing with no-records cases in Access

Some nice people in a public-interest group were kind enough to send me their data on the outside interests and committee assignments of state legislators, which is right up my alley. It is an Access database because that's how they did it.

Getting it to list everyone with an outside interest in banking, this is easy.

Getting it to list everyone assigned to a banking committee, this is easy.

What I'm having trouble with is generating the output that I want, which is a row for every legislator, whether or not they have an outside interest in banking, and whether or not they sit on the banking committee.

I can get pretty close to this, but I'm having problems with legislators who have no committee assignments (and so don't appear in that table) or claim to have no outside interests (and so don't appear in that table). These people are not showing up in the final result. This is bad.

So what's the easiest way to get Access to show me *every* legislator?

I have a suspicion that the easiest workaround is to add ~8500 records to the committee-assignment and outside-interest tables asserting that everyone is assigned to the NO_COMMITTEE committee, and everyone has an outside interest in NO_INTEREST, thus making sure that every legislator appears in both of these tables.

But if there's another way to beat Access into submission, I'd likely prefer to do that.

posted by ROU_Xenophobe to computers & internet (6 comments total)

No no no. You want what is called an outer join in SQL. Table A LEFT OUTER JOIN table B means, "all records from table A plus corresponding records from table B".

In Access-speak, what you want to do is click on the join - the line - between the two tables and change the join type.

In SQL editor mode, the SQL command should read "A LEFT JOIN B" or "A LEFT OUTER JOIN B" rather than "A INNER JOIN B".

Inner join means only the records from A that have corresponding records in B. That's what you've got now.
posted by jellicle at 2:42 PM on June 14, 2006

You want to investigate Outer (right) Joins.
posted by unixrat at 2:43 PM on June 14, 2006

Ta much. That seems to have it.

The "give me a list of everything meeting the criteria..." orientation of Access is giving me fits, since I want (and am used to) the "give me a list of everyone, and whether or not they meet the criteria..." world more common to statistical software.
posted by ROU_Xenophobe at 2:59 PM on June 14, 2006

Sounds like you already have it, but here's how to do this in Design View in Access without having to mess with the SQL.

Start a new query in design view and choose the tables you need; Legislators, committee-assignment, and outside-interest.

If relationships are already setup, you probably have one line going from legislators to both the other tables. Right click on one of these and choose the option which says "Show All records from Leglilators and only....blahblahblah". If you've done this right, the arrow should be pointing away from legislators.

Do the same for the second relationship and table, and you should be good to go, just pick your fields and see what it looks like.
posted by utsutsu at 3:54 PM on June 14, 2006

Yeah, that's what I did, except in the end I had to do it as a series of queries to avoid "ambiguous outer links" in one of my conditional statements.

I could do this in Stata, or probably R, I'm just unused to Access and its assumptions.
posted by ROU_Xenophobe at 4:34 PM on June 14, 2006

Hah, Leglilators... not sure how I missed that... anyways...

You can get rid of the ambiguous outer links by having all the outer joins pointing away from one main table. Also make sure the relationships on the tables are set up right. Right click on the relationships screen, choose Show All and make sure that there's nothing on there twice. I mention this because just last week I was getting an bizarre "ambiguous outer links" message and it turned out I had one table on the Relationships twice, with the relationship duplicated. The seperate / sub queries work too.
posted by utsutsu at 6:13 PM on June 14, 2006