Complex Access Query

Databases
1

First of all I’d like to thank those who have helped me through the last week of questions regarding access. I greatly appreciate it. Here is one of my last hurtles for this project.

The following query works great. 1 employee will have many assignments, 1 assignment to 1 record on tbl12. Now I’d like to also add in to this a table of comments. 1 record on table 142 may have many comments. I cannnot for the life of me add in the comments table so that it does not remove records from the query if a record does not have any comments. I get errors trying to switch it to a left / right join.


 SELECT tbl142Assignments.*, tbl142.*, tblEmployees.*, tblEmployees.ID
FROM tbl142 INNER JOIN (tbl142Assignments INNER JOIN tblEmployees ON tbl142Assignments.assignedTo = tblEmployees.ID) ON tbl142.ID = tbl142Assignments.ID
WHERE (((tblEmployees.ID)=Environ()));
2

What errors?
And can you post the query with the LEFT JOIN that is giving you errors?

3

Join Expression not supported is usually what I get. I’ve tried all kinds of different setups but am having trouble since access doesnt allow outter joins. In any case, I realize I have inner joins set up now but all should be left / right joins.


SELECT tbl142Assignments.*, tbl142.*, tblEmployees.*, tblEmployees.ID
FROM tbl142 LEFT JOIN (tbl142Assignments LEFT JOIN tblEmployees ON tbl142Assignments.assignedTo = tblEmployees.ID) ON tbl142.ID = tbl142Assignments.ID
WHERE (((tblEmployees.ID)=Environ()));

That works the way I want it to also but everything I have so far is 1 to 1 relationship really… While the comments will have 0 to many

4

of course access allows outer joins!!!

rewrite your FROM clause as follows:

SELECT something
     , anything
     , just_not_the_dreaded_evil_select_star
  [COLOR="Blue"]FROM ((
       tblEmployees 
INNER 
  JOIN tbl142Assignments 
    ON tbl142Assignments.assignedTo = tblEmployees.ID 
       )
INNER 
  JOIN tbl142 
    ON tbl142.ID = tbl142Assignments.ID
       )[/COLOR]
[COLOR="Red"]LEFT OUTER
  JOIN tblComments
    ON tblComments.tbl142ID = tbl142.ID[/COLOR]
 WHERE tblEmployees.ID = Environ()
5

Excellent. Thank you.

ps. * was just for readability and privicy :slight_smile:

6

:slight_smile: