Classroom Management Database

[matthew544]

Hi all, was hoping someone could provide a quick tip for an Access novice. I have a simple classroom management database, with three main tables: Students, Classes, Topics. The Students table is linked to the Classes table, so I can track which students attended which class(es). The Classes and Topics tables are also linked, so I can track which topic(s) was/were covered in which class(es). Is there an easy way to link the Students and Topics tables, so whenever a student is assigned to a class, a record will show that the student learned all the topics covered in that class? I would like to be able to go back later and see which students learned which topics.

Attached is a screenshot of my table relationships. Thanks.

[orange]

It seems from your diagram that Student and Topic are related by means of the relationships.
Consider Student 1 who is in Class 200 and Class 200 covers Topic 33 and Topic 34
In StudentsClasses there is a record with StudentId 1 and ClassID 200, and in ClassTopic there is a record with ClassID 200 and Topic 33 and a record with ClassID 200 and Topic 34.

So you create a query based on your relationships. Along these lines to find Topics by Student:
(there are other SQL options(Inner Join), but this is trying to show related fields)

SELECT Student.First, Student.Last,Topics.Topic
FROM  Students, StudentsClasses, Classes, ClassTopic, Topics
WHERE
Students.StudentID = StudentClasses.StudentID   AND
StudentClasses.ClassID = Classes.ClassID  AND
Classes.ClassID = ClassTopic.ClassID

How do you know the Student attended a specific Class?

Good work of the diagram! Good luck with the project.

[matthew544]

Thanks, exactly what I needed. Not sure why I didn't think of just using a query. Seems pretty obvious now.

I did have to add the following to the end of your query to get it to work correctly:

AND ClassTopic.TopicID = Topics.TopicID

How do you know the Student attended a specific Class?

I am using a Classes form with a Students subform. Create a class in the form, add students in the subform. Creates a record for each attendee in the StudentsClasses table.

[orange]

Good call!!

I suggest you add a field to the StudentClasses table-- something like AttendedYN. Boolean YesNo datatype.
That way you can also identify which Students were not present for certain Classes.

Take a look at this data model.

[matthew544]

That's a good idea. Thanks.

[matthew544]

I have another question, as a follow-up to my original question. So I now have a query with results that look something like this:

Last	First	Middle	Topic	Attended
Smith	John	A.	Math	Y
Doe	Jane	A.	Math	Y
Public	John	Q.	Science	Y
Doe	Jane	A.	Science	Y

Is there a way to pull that data into a matrix-style report? Or maybe a matrix query first, that I can then use to create a report? Something like this:

Last	First	Middle	Math	Science	Reading
Smith	John	A.	X
Doe	Jane	A.	X	X
Public	John	Q.		X

I would also like to be able to apply a filter to it, which would force the user to select specific students (or all students) to display when opening this report. Using a basic report would be fine for the example above, but I'm going to end up with a lot of students and a lot of topics, and will need a way to quickly see which student still has to learn which topic.

Thanks again for all the help. It is very much appreciated.