If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Test results query
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. |
#2
|
|||
|
|||
Test results query
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" wrote in message ... 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. |
#3
|
|||
|
|||
Test results query
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" wrote: 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" wrote in message ... 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. |
#4
|
|||
|
|||
Test results query
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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#5
|
|||
|
|||
Test results query
I am receiving a message: "JOIN expression not supported"!!!
"KC-Mass" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#6
|
|||
|
|||
Test results query
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" wrote in message ... I am receiving a message: "JOIN expression not supported"!!! "KC-Mass" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#7
|
|||
|
|||
Test results query
Yeah that's true. I have a problem with my data structure. I will revise it.
Thanks for the thelp. "KC-Mass" wrote: 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" wrote in message ... I am receiving a message: "JOIN expression not supported"!!! "KC-Mass" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|