Outer join not working in all queries

[lscheer]

I have a bunch of queries set up with outer joins so that they should return all records from one table and only matching records from another table. The problem is, they aren't all working as they are supposed to be (and oddly, some of them are). Here is the SQL statement I'm using and the only thing that changes for each query is the joined field in the secondary table (in this case "Lodging").

I have been trying to research it some, and my SQL skills are very minimal, so I'm at a loss. I have seen suggestions to use UNION statements but when I try to implement it in this SQL I get error messages about "RatingValue" not being part of an aggregate function...

Any help is greatly appreciated.

SELECT tblEvalRatings.RatingValue, tblEval_Participants.Lodging, Count(tblEval_Participants.Lodging) AS CountOfLodging, tblEval_Participants.TrgCode, tblEvalRatings.Question
FROM tblEvalRatings LEFT JOIN tblEval_Participants ON tblEvalRatings.RatingValue = tblEval_Participants.Lodging
GROUP BY tblEvalRatings.RatingValue, tblEval_Participants.Lodging, tblEval_Participants.TrgCode, tblEvalRatings.Question, tblEvalRatings.RatingSequence
HAVING (((tblEval_Participants.TrgCode)=[Forms]![frmEvalGuide].[trgcode]) AND ((tblEvalRatings.Question) Like "*lodging*") AND ((tblEvalRatings.RatingSequence)>0)) OR (((tblEval_Participants.TrgCode) Is Null) AND ((tblEvalRatings.Question) Like "*lodging*") AND ((tblEvalRatings.RatingSequence)>0))
ORDER BY tblEvalRatings.RatingSequence;


I should also add that the Outer Join seems to be working fine when I remove all criteria from the query design (fine is not that it gives me the results that I need, but it does return all records from one table and matching from another, just doesn't work when I set the criteria I need)...

 

[ByteMyzer]

This is slightly on the more advanced side, but try the following SQL statement:

SELECT T1.RatingValue, T2.Lodging,
       Count(T2.Lodging) AS CountOfLodging, T2.TrgCode, T1.Question
FROM tblEvalRatings AS T1
LEFT JOIN (
            SELECT *
            FROM tblEval_Participants
            WHERE TrgCode = [Forms]![frmEvalGuide].[trgcode]
          ) AS T2 ON T1.RatingValue = T2.Lodging
GROUP BY T1.RatingValue, T2.Lodging, T2.TrgCode,
         T1.Question, T1.RatingSequence
HAVING T1.Question Like "*lodging*"
   AND T1.RatingSequence > 0
ORDER BY T1.RatingSequence;

 

[lscheer]

AWESOME! Works perfectly on all the queries. I wish I knew what the difference was between what I had set up and what you set up, but I'm just glad it's working now! Thanks so much!!!!

 

[ByteMyzer]

You're very welcome, I'm glad it worked for you.

Your original SQL statement works only if the corresponding records in table tblEval_Participants do not have a TrgCode other than the code specified on Form frmEvalGuide. If you have a corresponding record with a different TrgCode, it will not return Null, and it will not return a matching record.

What I did was to report only on a subset of the data from table tblEval_Participants with a matching code by means of an inline subquery, then creating a LEFT JOIN from table tblEvalRatings to the subquery. The same thing could be accomplished with two separate queries, if, for example, I create the first query, thus:

Query Name: qryEval_Participants:

SELECT *
FROM tblEval_Participants
WHERE TrgCode = [Forms]![frmEvalGuide].[trgcode];

...then create the second query, thus:

Query Name: qryEvalMain

SELECT T1.RatingValue, T2.Lodging,
       Count(T2.Lodging) AS CountOfLodging, T2.TrgCode, T1.Question
FROM tblEvalRatings AS T1
LEFT JOIN qryEval_Participants AS T2 ON T1.RatingValue = T2.Lodging
GROUP BY T1.RatingValue, T2.Lodging, T2.TrgCode,
         T1.Question, T1.RatingSequence
HAVING T1.Question Like "*lodging*"
   AND T1.RatingSequence > 0
ORDER BY T1.RatingSequence;

 

[lscheer]

Thanks for the explanation! That explains why, when I try to use a parameter prompt in the query design pane, it doesn't return the null value records, too, I guess.

Again, thanks for your help on this--much appreciated!