SQL Question

[avagodro]

I have a reference in a form that is starting to drive me crazy. Usually I can resolve this, but maybe its from having a long week and not thinking clearly.
Anyway, in the query I have the following tables:

tblResponses
-AssociateRef
-QstnID
-Chosen Value
-ResponseDate

tblResponsesList
-QstnID
-Choices

Here is the SQL that I have, then I will explain my dilema:

SELECT DISTINCTROW tblResponsesList.Choices, tblResponses.[Chosen Value]
FROM tblResponses RIGHT JOIN tblResponsesList ON tblResponses.[Chosen Value] = tblResponsesList.Choices
WHERE (((tblResponses.[Chosen Value]) Is Null) AND ((tblResponsesList.QstnID)=[Form].[QstnID]))
ORDER BY tblResponsesList.Choices;

This SQL is the Row Source for the first of two listboxes. It was working fine, until it came to a Questions with the same choices (True or False), even though it was a different QstnID.
I am thinking that what is needed is the right join, as it is, check the QstnID from tblResponsesList with [Form].[QstnID] as it is now, but also check [QstnID] from tblResponses. If the QstnID exists, then it would filter it.
I'm not sure. I'm at a bit of a loss here. Any suggestions?

 

[lynchg]

Your query is going to bring back all of the records in tblResponsesList where there is NOT a corresponding record in tblResponses.

This is the phrase that makes that so:

(tblResponses.[Chosen Value])

Checking QstnID from tblResponses as your query is currently written will always return 0 rows.

You are not clear enough in what you want to accomplish for me to offer anything else.

 

[avagodro]

I've done a modification to my table structures:

tblResponses
-AssociateRef
-QstnID
-KeyID
-ResponseDate

tblResponsesList
-KeyID (AutoNumber)
-QstnID
-Choices

I am working on redoing the SQL utilizing KeyID as the join rather than Choices.