puzzling over a query
October 28, 2011 8:44 AM Subscribe
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.
posted by hotelechozulu to Computers & Internet (5 answers total) 2 users marked this as a favorite
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
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.