puzzling over a query

October 28, 2011 8:44 AM

I'm trying to write a SQL query on an 8 year old broken Access database that I didn't build, now on Access 2007. It's fun, I tell you what. I'm having trouble with a left outer join.

Here's the tables I'm trying to join:

Crews table: crew number, group the crew belongs to, main office location, and the buildings served.
Employees table: crew number, employee name, employee's supervisor, and employee title.

I want to show a list of all the crews, the group the crew belongs to, with crew leader, main office location, and buildings served. Some of the crews don't have a crew leader. I want to order by crew and show each crew leader's supervisor and group (so I can group on these in a report). I need to show the crews without a crew leader, with the buildings served.

I'm trying to do a left outer join, but when I limit on employee title to "crew leader" (out of all the other titles), it knocks out all the crews without a crew leader. Close, but not quite right. Here's my SQL:

SELECT [tblEmployeeInfo.first] & " " & [tblEmployeeInfo.last] AS Name, tblEmployeeInfo.title, tblEmployeeInfo.reports_to, tblCrews.crew, tblCrews.buildings, [tblCrews.office] & " " & [tblCrews.room] AS location, tblCrews.group
FROM tblCrafts LEFT OUTER JOIN tblEmployeeInfo
ON tblCrews.crew=tblEmployeeInfo.crew
WHERE (((tblEmployeeInfo.title)="Crew Leader"))
ORDER BY tblEmployeeInfo.crew;

I've tried UNION, UNION ALL, flipping my field names. Any ideas? It looks a little funky because it's an Access query, but this one works, just not the way I want it to. Any advice?

If there's a better community to post this on, I'd appreciate that advice too.

posted by hotelechozulu to computers & internet (5 answers total)

stackoverflow was what I was thinking of; I will post this there as well. Sorry to keep putting these highly specific tech questions in here.
posted by hotelechozulu at 8:51 AM on October 28, 2011

Is the "tblCrafts" (after the FROM, where one would expect a "tblCrews") a typo in your question, or is that a mistake in the actual query?
posted by teg at 9:01 AM on October 28, 2011

gack, typo. Its actually "crafts" (that's what we call our crews) on the table, but that wouldn't make sense to anyone here, so I changed it to "crews" for this question.

Correct query:

SELECT [tblEmployeeInfo.first] & " " & [tblEmployeeInfo.last] AS Name, tblEmployeeInfo.title, tblEmployeeInfo.reports_to, tblCrews.crew, tblCrews.buildings, [tblCrews.office] & " " & [tblCrews.room] AS location, tblCrews.group
FROM tblCrews LEFT OUTER JOIN tblEmployeeInfo
ON tblCrews.crew=tblEmployeeInfo.crew
WHERE (((tblEmployeeInfo.title)="Crew Leader"))
ORDER BY tblEmployeeInfo.crew;
posted by hotelechozulu at 9:07 AM on October 28, 2011

The problem is the WHERE clause, because if there is no crew leader, then the Job Title cannot possibly be 'Crew Leader'.
You need to rewrite it like this:

WHERE (((tblEmployeeInfo.title)="Crew Leader")) OR (tblEmployeeInfo.title IS NULL)

Also, I don't bother writing queries in the SQL text editor in Access. Use the graphical interface. I know it is annoying and counter-intuitive, but the SQL text interface is unimaginably horrible. It will strip out all of your tabs and line breaks, and you will find that it is impossible to maintain your code this way.
posted by Acheman at 9:07 AM on October 28, 2011

What Acheman said. But if your EmployeeInfo table has existing records with a NULL title, you may need to rewrite it more like:

WHERE (((tblEmployeeInfo.title)="Crew Leader")) OR (tblEmployeeInfo.EmployeeID IS NULL)

assuming there's some value like EmployeeID that can't be NULL.
posted by teg at 9:17 AM on October 28, 2011