Solved How to show records from one table when no record is in another (1 Viewer)

[tmd63]

I have a query that works fine when there is data in both tables, but not when there is no records in tblTest.

SELECT tblQuestions.ID, tblQuestions.Question, tblQuestions.Module
FROM tblQuestions, tblTest
WHERE (((tblQuestions.ID)<>[tblTest].[lngQuestionID]) AND ((tblQuestions.Module)=[Forms]![frmTest]![cboModule]));

tblTest does not have a relationship with tblQuestions but does store the QuestionID once an test question is selected. But at the start of the Test, the tblTest table is empty. At this point, no questions are being displayed because of the 'From tblQuestions, tblTest' entry. If I remove tblTest the question appear. However I need to restrict the questions to ones not selected before (hence the test tblQuestionsID<>tblTest.lngQuestionID).
Any suggestions on how to make the questions appear when tblTest is empty?

 

[Grumm]

Maybe that a Left join will help you ?

Example :

SELECT tblQuestions.ID, tblQuestions.Question, tblQuestions.Module
FROM tblQuestions LEFT JOIN tblTest ON tblQuestions.ID<>tblTest.lngQuestionID
WHERE (((tblQuestions.Module)=[Forms]![frmTest]![cboModule]));

 

[tmd63]

Thanks that appears to work a treat.

Opps. I spoke too soon. After entering 2 questions and answers, the query now produces 2 duplicate questions apart from the 2 already answered.

 

[tmd63]

Just to clarify. I have 3 tables. Questions, Answers and Test. Question are linked to Answers Each question has 4 Answers. There are 115 Questions in 9 Sections, Some Sections have 15 Questions and some have 10.
I need to select the Section, and then have the Question combo box list the questions in that section. BUT, it must list all the question if none have been answered before AND it must not list any previously answered questions.
Section combo box lists the section (cboModule).
Question combo box is to be used to select a question (cboQuestion).

 

[Grumm]

In that case your SQL is not correct.
Do you have in the table tblTest an ID of the question ?
If that is the case, then this would work beter :

SELECT tblQuestions.ID, tblQuestions.Question, tblQuestions.Module
FROM tblQuestions LEFT JOIN tblTest ON tblQuestions.ID=tblTest.lngQuestionID
WHERE ((tblQuestions.Module)=[Forms]![frmTest]![cboModule]) AND tblTest.lngQuestionID IS NULL;

If that still doesn't work, can you post a few samples of the 3 tables ? That way I can create a dummy project and see what you actually want.
(Or you can post a version of what you already have)

 

[tmd63]

Completed my database. It is an Access 2007-2016 format with tricks that many may find useful