Really big student class lists.

[Jamie Risk]

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

 

[schasteen]

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.

 

[Jamie Risk]

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.

 

[schasteen]

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]

I'll give it a shot.

Thanks,
- Jamie

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;

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 Risk]

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

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;

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.

 

[schasteen]

Your statement appears to be OK. The [What_student_ID] is a condition. Left
in this condition you should be prompted for a value. You could replace this
with a referance to a control on a form or leave it blank and return all of
the values. The problem with returning all the values is that you will get a
large amount of data to try and organize, but may be handled well by grouping
if you are creating a report.

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

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;

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