Test results query

[Amer]

I am creating a query to show the test results of a training course.

I am using the following tables:

tabGrouping tabGradeBook tabTests
GroupingID (key) FileNo (key) TestID (key)
GroupRef TestID (key) TestType
FileNo Score GroupRef
FullMark

The SQL script for the query is as follows:

SELECT tabGradeBook.FileNo, tabGrouping.GroupRef, tabTests.TestID,
tabTests.FullMark, tabGradeBook.Score, tabTests.TestType
FROM (tabTests INNER JOIN tabGradeBook ON tabTests.TestID =
tabGradeBook.TestID) INNER JOIN tabGrouping ON (tabGradeBook.FileNo =
tabGrouping.FileNo) AND (tabTests.GroupRef = tabGrouping.GroupRef);

Actually, I need this query to show the file numbers (FileNo) of those who
have not attended the test. i.e have no scores at all in the "score" field of
the "tabGradeBook" table.

 

[KC-Mass]

Try This (only added WHERE clause):

SELECT tabGradeBook.FileNo, tabGrouping.GroupRef, tabTests.TestID,
tabTests.FullMark, tabGradeBook.Score, tabTests.TestType
FROM (tabTests INNER JOIN tabGradeBook ON tabTests.TestID =
tabGradeBook.TestID) INNER JOIN tabGrouping ON (tabGradeBook.FileNo =
tabGrouping.FileNo) AND (tabTests.GroupRef = tabGrouping.GroupRef)
WHERE Is Null(tabGradeBook.Score);

Regards
Kevin

 

[Amer]

This would work if I created a record for the trainees in the tabGradeBook
table and left the score filed empty.

As I inidcated earlier I need the query to show me the file numbers of
thouse who have not attended the test from the beginning, otherwise they
would have an entry in the corresponding tabGradeBook table. Those who have
not attended the test would have any reference in the tabGradeBook table.

 

[KC-Mass]

Change your INNER JOIN to a RIGHT JOIN when joining the tabGradeBook

As I told you I only added the WHERE clause - you remain responsible for the
basic query.

 

[Amer]

I am receiving a message: "JOIN expression not supported"!!!

 

[KC-Mass]

I don't know your data structures so I am going to create and use a simple
example.
You can adapt this to real data.

tblTests has three fields StudentID, Course, TestScore. It has two records.

StudentID, Course, TestScore
1 Eng 100
1 Geo 80

tblGradeBook has two fields StudentID and Course. It has three records.
StudentID Course
1 Geo
1 Eng
2 Math

The SQL looks like:
SELECT tblGradeBook.StudentID, tblGradeBook.Course, tblTests.Score
FROM tblTests RIGHT JOIN tblGradeBook ON
(tblTests.Course = tblGradeBook.Course) AND
(tblTests.StudentID = tblGradeBook.StudentID)
WHERE (((tblTests.Score) Is Null));

The Result is one record

StudentID Course Score
2 Math

The Score field in null because there is no score recorded for that
StudentID, Course combo but it is brought into the dataset as a Null.

 

[Amer]

Yeah that's true. I have a problem with my data structure. I will revise it.

Thanks for the thelp.