MS Access Query Query

February 20, 2012 8:46 AM

Complicated (or probably not complicated) MS Access question that I'm not sure how to google for.

So I have a table called Sequences which contains SeqNum (primary key), ID (a train service ID) and Node (a place the train goes through).

I have another table called Area Boundaries which has a smaller subset of nodes and an associated area.

I want to do a query which shows all the records from Sequences and then adds on the end if there is an associated area. Roughly 1 node in 30 has an associated area. If there isn't an associated area I just want a blank entry.

Right now I am getting a result which shows only the nodes with the asociated area and not the whole thing. I've tried messing about with "Is Null", "Is Not Null" and " " " ", but to no avail.

posted by Just this guy, y'know to Computers & Internet (5 answers total)

Best answer: What you want is to play with the join properties (you can right click on the join in your query) to include ALL from Sequences. If you google join properties access you can find examples for your version.
posted by jeather at 8:52 AM on February 20, 2012 [1 favorite]

Response by poster: Thanks! Such a simple solution. That has been annoying me for HOURS!

Problem is well and truly 100% perfectly solved.
posted by Just this guy, y'know at 8:56 AM on February 20, 2012

SELECT Sequences.*, Areas.Area
FROM Sequences INNER JOIN Areas ON Sequences.Node = Areas.Node;

posted by desjardins at 8:56 AM on February 20, 2012

desjardins: he needs a left join, not an inner join

OP: for the love of God, rename "ID" to "TrainServiceID" everywhere
posted by thelonius at 9:09 AM on February 20, 2012 [6 favorites]

Response by poster: Thanks, thelonius, I have actually done that in most places, but your point is well made.
I'll go and make sure that's the case.
posted by Just this guy, y'know at 11:50 PM on February 20, 2012