two tables

S

Steve

I need to do something that I thought would be relatively simple that is now
starting to drive me nuts.

I have two tables I need to compare, and create a recordset from when a
specified text field contains the same information.

Table 1 (name=questions) contains the following fields: questionID, qExamID,
qChapter, qNumber, question, qAnswer
Total number of questions = 12

Table 2 (name=studAnswers) contains: ansID, ansStudentID, ansExamID,
ansQuestionNo, ansQuestion

I need to return a recordset where table1.qAnswer equals table2.ansQuestion.
Both of these fields are text fields.

If I use this query specifying the student ID:
SELECT DISTINCT studAnswers.ansStudentID, questions.qExamID,
studAnswers.ansQuestionNo, studAnswers.ansQuestion
FROM questions INNER JOIN studAnswers ON questions.qAnswer =
studAnswers.ansQuestion
WHERE (((studAnswers.ansStudentID)=60))

I get a recordset with 149 records in it..clearly way too many.

If I use this query where I am specifying the student ID and examID:
SELECT DISTINCT studAnswers.ansStudentID, questions.qExamID,
studAnswers.ansQuestionNo, studAnswers.ansQuestion
FROM questions INNER JOIN studAnswers ON (questions.qExamID =
studAnswers.ansExamID) AND (questions.qAnswer = studAnswers.ansQuestion)
WHERE (((studAnswers.ansStudentID)=60))

I get 12 records returned, which is clearly closer than the original
recordset, but one of the answers in studAnswers.ansQuestion is
intentionally wrong, so I should be getting only 11 records, instead of 12.

I've tried many variations on the above, but none have returned the correct
number of records.

I would sure appreciate any help I can get on this.

TIA

Steve
 
Top