I can't figure out what to replace [What_student_xxx] with.
I'd like to repost your code, with the hope of removing ambuigity in my
understanding, and to use the ID fields for the boolean tests. Does the
following make (SQL) sense?
SELECT Attendance.Student_ID AS Attn_Stu_ID,
Student.Student_ID AS Stu_ID,
Class.Subject AS Class_Subject,
Students_1.Student_ID AS Stu1_ID
FROM Students INNER JOIN (Class INNER JOIN ((Attendance LEFT JOIN Attendance
AS Attendance_1 ON Attendance.Class_ID = Attendance_1.Class_ID) LEFT JOIN
Students AS Students_1 ON Attn_Stu_ID = Stu1_ID) ON
Class.Class_ID = Attendance.Class_ID) ON Stu_ID =
Attn_Stu_ID
WHERE ((Attn_Stu_ID=[What_student_ID]) AND
((Stu1_ID)<>Stu_ID))
ORDER BY Stu_Surname, Stu_Name, Class_Subject;
If I got that right, I still don't know what [What_student_ID] would be in
the query. Could it be just [Stu_ID]?
- Jamie
schasteen said:
I think this will work. If you take out the where clause it will give you
a
lot of records and you will need to manipulate them somehow.
SELECT Attendance.Student_ID, Students.Surname, Students.Name,
Class.Subject, Students_1.Surname, Students_1.Name
FROM Students INNER JOIN (Class INNER JOIN ((Attendance LEFT JOIN
Attendance
AS Attendance_1 ON Attendance.Class_ID = Attendance_1.Class_ID) LEFT JOIN
Students AS Students_1 ON Attendance_1.Student_ID = Students_1.Student_ID)
ON
Class.Class_ID = Attendance.Class_ID) ON Students.Student_ID =
Attendance.Student_ID
WHERE (((Students.Surname)=[What_student_surname]) AND
((Students.name)=[What_student_name]) AND
((Students_1.Surname)<>[Students.Surname]) AND
((Students_1.Name)<>[Students.Name]))
ORDER BY Students.Surname, Students.Name, Class.Subject;
Jamie Risk said:
Whoops, your right, it was an accidental ommision - the connection is is
the
Attendance table, which _should have_ been;
CREATE TABLE Attendance (Att_ID Autonumbered Primary Key, Student_ID,
Class_ID)
with a compound index on the Student_ID and Class_ID.
The other three tables are still:
CREATE TABLE Teachers (Teach_ID Autonumbered Primary Key, Name, Surname)
CREATE TABLE Students (Student_ID Autonumbered Primary Key, Name,
Surname)
CREATE TABLE Class (Class_ID Autonumbered Primary Key, Room, Time,
Subject,
Teach_ID)
The Class has a compound key on the fields Teach_ID, Time and Room so
that a
teacher can't appear in two places at once.
Jamie,
I do not see how you have students related to the class. Your class
table
either needs to have a referance to the students or attendance table.
Maybe
you have this already but it is not listed. You will need this
relationship
before you can run your query.
:
I have four normalized tables,
Teachers (Teach_ID Autonumbered Primary Key, Name, Surname)
Students (Student_ID Autonumbered Primary Key, Name, Surname)
Class (Class_ID Autonumbered Primary Key, Room, Time, Subject,
Teach_ID)
Attendance (Att_ID Autonumbered Primary Key, Student_ID)
The Class has a compound key on the fields Teach_ID, Time and Room so
that a
teacher can't appear in two places at once.
My query to get teachers schedules has been straightforward enough,
but
how
do I get schedules for each student in each class that includes every
other
student in tha class?
- Jamie
My question is this
