Leonard:
There are a number of things wrong with your query:
1. The tblAssessmentItems table should be LEFT JOINed to the subquery, not
INNER JOINed.
2. The subquery should be given an alias so that it can be referred to in
the LEFT JOIN clause. I've use the alias StudentResults.
3. The subquery is joined to the tblAssessmentItems table on the
AssessmentItemID columns, not the StudentID columns.
4. The subquery should be restricted to one StudentID (you gave 13 as an
example) but rather than a literal value in reality you'd use a reference to
a control on your form as a parameter as Tina explained.
5. Ordering the query is optional but if you do so it ensures that the rows
for those assessment taken by the student appear together before the rows for
those not yet taken.
So putting all that together gives us:
SELECT
StudentResults.StudentID,
tblAssessmentItems.AssessmentItemID,
tblAssessmentItems.AssessmentTitle,
StudentResults.Result,
FROM tblAssessmentItems
LEFT JOIN
(SELECT
tblStudents.StudentID,
tblJunctionAssessmentResults.AssessmentItemID,
tblJunctionAssessmentResults.Result
FROM tblStudents INNER JOIN tblJunctionAssessmentResults
ON tblStudents.StudentID = tblJunctionAssessmentResults.StudentID
WHERE tblStudents.StudentID = Forms!NameOfForm!NameOfControl)
AS StudentResults
ON tblAssessmentItems.AssessmentItemID = StudentResults.AssessmentItemID
ORDER BY StudentID DESC, AssessmentTitle;
Lets examine how it works;
1. The subquery returns rows for the assessments which have been taken by
the student by INNER JOINing the tblStudents and tblJunctionAssessmentResults
tables on the StudentID columns.
2. The subquery returns the StudentID, AssessmentItemID and Result columns.
The StudentID and Result columns are included because these are needed to be
returned by the outer query; the AssessmentItemID column is included because
it is this on which the tblAssessmentItems table is joined to the subquery,
so it need to be in the latter's set of returned columns.
3. The tblAssessmentItems table is LEFT JOINed to the subquery, which in
the join is referred to by its alias StudentResults. A LEFT OUTER JOIN (the
OUTER is optional) returns *all* rows from the left side of the join and the
matching rows from the right side. So all rows from tblAssessmentItems are
returned, but are only joined to those returned by the subquery where the
AssessmentItemID values match. Consequently the result set of the outer
query will be all rows from AssessmentItemID with values in the
AssessmentItemID and AssessmentTitle columns in each row, but with valaues
in the StudentID and Result columns only for those where the student has
undertaken those assessments, these columns being NULL otherwise.
Ken Sheridan
Stafford, England
Leonard said:
Ken, thanks for your reply.
It would be great to achieve this in 1 query.
I had a go at what you suggested but could only get the results already
entered. That is:
SELECT tblJunctionAssessmentResults.StudentID,
tblAssessmentItems.AssessmentItemID, tblAssessmentItems.AssessmentTitle,
tblJunctionAssessmentResults.Result
FROM tblStudents INNER JOIN (tblAssessmentItems INNER JOIN
tblJunctionAssessmentResults ON tblAssessmentItems.AssessmentItemID =
tblJunctionAssessmentResults.AssessmentItemID) ON tblStudents.StudentID =
tblJunctionAssessmentResults.StudentID;
Any further suggestions would be appreciated.
Leonard
:
Leonard:
You can in fact do this in a single query by left outer joining the
tblAssessmentItems table to a subquery which returns the students assessment
data already entered. e.g.
SELECT StudentName, Assessment, Result
FROM tblAssessmentItems
LEFT JOIN
(SELECT StudentName, AssessmentID, Result
FROM tblStudents INNER JOIN tblJunctionAssessmentResults
ON tblStudents.StudentID = tblJunctionAssessmentResults.StudentID
WHERE tblStudents.StudentID = 13) AS StudentResults
ON tblAssessmentItems.AssessmentID = StudentResults.AssessmentID
ORDER BY StudentName DESC, Assessment;
By ordering the rows by StudentName in descending order this causes the rows
for those assessments for which there are results to be listed first followed
by those Assessments for which there are as yet no results data for the
student, due to the fact that Nulls sort before values. You'd need to
substitute your own column names where necessary of course; remember that
names with spaces or other special characters need to be delimited with
brackets [like this]. You can incude other columns in the SELECT clauses of
the outer or subquery if you wish.
Ken Sheridan
Stafford, England
:
Hi
I'm a teacher creating a database to store the results of my students.
I have a tblStudents - listing students
tblAssessmentItems - listing tests, assignments etc
tblJunctionAssessmentResults - many to many table linking students,
assessment items and results
What I want to do is create a query to list the results of say
[studentid]=13 as well as list the assessment items I have not yet entered
data for that student.
I want to disply this in a listbox.
I have successfully done this with 2 queries. The first query joined
tblAssessmentItems and tblJunctionAssessmentResults for [studentid]=13
then
the second query joined tblAssessmentItems again with the first query but
with a 'include all records from tblAssessmentItems.
This created a query that showed all results for [studentid]=13 and also
listed those items that I have not yet entered results.
However, I couldn't figure out how to display this with a listbox as the
listbox was linked to the second query => I needed to change the [studentid]
on the first query to make it work.
Any help would be very grateful.
Leonard
