Query on criteria from two tables

[jpkeller55]

Hi,



I have two tables where one table is a list of questions (Table1) and the other table is a list of responses (Table2) that are related to the questions. The related field is QuestionID and is a one to many relationship. These questions and responses will be displayed as a survey of sorts and only certain questions should be displayed and certain responses should be displayed based on certain criteria. See the attached file that shows the table structure with some fake data and the desired query results based on the criteria being "like red" or "is null". I don't know how to show the tables clearly in this question so I am resorting to the Word attachment. This is the code I am starting with but I can't figure out how to apply the criteria to the ResponseCriteria to filter out the response without affecting the QuestionCriteria.

SELECT Table1.QuestionID, Table1.QuestionText, Table1.QuestionCriteria, Table2.ResponseText, Table2.ResponseCriteria
FROM Table1 INNER JOIN Table2 ON Table1.QuestionID = Table2.QuestionID
WHERE (((Table1.QuestionCriteria) Like "*red*" Or (Table1.QuestionCriteria) Is Null));

[jpkeller55]

Answered my own question believe it or not! Simple matter of adding the word And plus the additional criteria! Woo Hoo!

SELECT Table1.QuestionID, Table1.QuestionText, Table1.QuestionCriteria, Table2.ResponseText, Table2.ResponseCriteria
FROM Table1 INNER JOIN Table2 ON Table1.QuestionID = Table2.QuestionID
WHERE (((Table1.QuestionCriteria) Like "*red*" Or (Table1.QuestionCriteria) Is Null)) and
(((Table2.ResponseCriteria) Like "*red*" or (Table2.ResponseCriteria)Is Null));

[RuralGuy]

So...I would guess this thread is ready for you to follow the link in my sig and mark as Solved.

[jpkeller55]

Thanks for the reminder...in my excitement I forgot to mark as solved.

[RuralGuy]

Thank you.