A form to list and edit multiple test scores for multiple students.

[gdpoc]

This is probably an easy question to answer for someone who knows what they're doing, but for me it's gotten rather frustrating. I need a cigarette.

I'm not an access guru, rather just creating this for my course.

I have a database and it has three tables that are applicable

1. Student Roster
Student Key | SSN | Currentclass | Pclass1 | Pclass2| Pclass 3| ___
Pclass 4 | Pclass 5 | Student Name | Phone Number | etc... some other stuff



2. TestStudent
Test Key | TestOrder | TestName | Block | Course |Gradeable

3. Completed Test Student
Student Lock | Test Lock | TestDate | Score | Remarks

Now for my issue. I am in the midst of creating a form to show an entire class (Anywhere between 5 - 18ish students). I want to be able to show, on one form, the grades on 'EVERY' test that each student in the class has taken. I know that this is very possible to do on a report but to meet the needs of the user it has to be editable, thus a form. I tried to put a continous form with a subform and as you all probably know Access told me, politely, to go have relations with myself.

I then spent the better part of 8 hours trolling google and forums to see if someone elses pains could keep me from beating my head into a wall but no luck.

Is there a way for me to do this? Either by working weird magic with forms or by programatically creating controls with vba?

[jzwp11]

Welcome to the forum!

Before getting to your problem, I see some potential issues with your table structure. First, when I see sequentially numbered fields (Pclass1, Pclass2 ...), it is usually a sign that your design is not normalized. This site has an overview on normalization. So what type of information is in the fields Pclass1, Pclass2 etc.?

It is also not clear to me how the 3 tables relate to one another.

Just going by what you described...

Since a student can take many tests and many students can take the same test, you have a many-to-many relationship between tests and students, so the way to handle this is with a junction table.

If you are exclusively dealing only with students and not any other people then I would have the student table as follows. If you are dealing with other people as well (teachers, administrators, parents etc.) then I would generalize the table to people (tblPeople) and have a field that identifies the type of person

tblStudents (hold only the information pertinent to the per
-pkStudentID primary key, autonumber (student key?)
-txtSSN
-txtFName
-txtLName

A table to hold basic information about each test

tblTest
-pkTestID primary key, autonumber
-txtTestName

Now the junction table

tblStudentTests
-pkStudentTestID primary key, autonumber
-fkStudentID foreign key to tblStudents (long number integer datatype)
-fkTestID foreign key to tblTest (long number integer datatype)
-dteTest (test date)
-spScore (I assume a single precision number datatype field)

I'm not sure what the other fields in your table represent, so you will have to provide more details on that.

[gdpoc]

Just to clarify for my table structure.

1. Student Roster
- Student Key (Primary Key, autonumber)
- SSN (Text)
- Currentclass (Text)
- Pclass1 (Text)
- Pclass2 (Text)
- Pclass 3 (Text)
- Pclass 4 (Text)
- Pclass 5 (Text)
- Student Name (Text)
- Phone Number (Text)
- etc... some other stuff

The fields PClass 1, 2, 3, 4, and 5 all refer to prior classes. In this course when a student fails a test they are placed back into a younger? class so they can revisit the course material a second time. Classes are named by their start date in YYMMDD format. So a student might start in class 110930 and upon failure of a benchmark would be placed into class 111025. The PClass fields are placed there for the instructor to be able to A. See the current class the student is in and B. See the previous classes the student has been in. There is also a class table with pertinent data about that class (i.e. graduation dates etc...)

2. TestStudent
- Test Key (Primary Key Autonumber)
- TestOrder (Test sequence in the course, integer)
- TestName (Text)
- Block (Course is portioned out, this is which portion the test is in, integer)
- Course (We teach multiple courses, ref above, integer)
- Gradeable (Yes/No, used to distinguish which grades to use for average calculation)

3. Completed Test Student
- Student Lock (Foreign Key to the student table)
- Test Lock (Foreign Key to the test table)
- TestDate (General date)
- Score (Long Integer)
- Remarks (Text)


I am an Access beginner but the only real difference I can see from what I had and what you suggested is an additional autonumbered field in my completed test student table. Is this necessary? Currently the two foreign keys comprise a composite primary key.
I don't want the end user to have access to the table thus the necessity of building a form.
My intention is to have a form that would look almost like this:

Let's say that class 110930 has 4 students in it.

TestA Test B Test C Test D
Stu1 100 72 85 100
Stu2 97 85 100 100
Stu3 85 93 95 100
Stu4 100 68 65 68

I have been able to create something similar in a form with one spectacular difference. It lists the tests that the students have taken by the student, but listed individually, i.e.

TestName Score
Stu1 testA 100
Stu1 TestB 72
Stu1 TestC 85
Stu1 TestD 100
etc... etc... for each student.


Sorry for the delay btw, I've been out the last couple of days. Wife just had a baby.

[jzwp11]

Sorry for the delay btw, I've been out the last couple of days. Wife just had a baby.

Congratulations!

I am an Access beginner but the only real difference I can see from what I had and what you suggested is an additional autonumbered field in my completed test student table. Is this necessary? Currently the two foreign keys comprise a composite primary key.

The structure I proposed was not finished yet since I was waiting for your response.

The fields PClass 1, 2, 3, 4, and 5 all refer to prior classes.

Since a student can have many classes (prior or current) then that describes a one-to-many relationship. Furthermore, if a class is applicable to other students you have another one-to-many relationship. If you have 2 one-to-many relationships between the same two entities (students/classes) then you have a many-to-many relationship which is handled with a junction table.



1. Student Roster
- Student Key (Primary Key, autonumber)
- SSN (Text)
- Student Name (Text)
- Phone Number (Text)

tblClasses
-pkClassID primary key, autonumber
-txtClass

...the junction table

tblStudentClasses
-pkStudentClassID primary key, autonumber
-fkStudentKey foreign key to student roster
-fkClassID foreign key to tblClasses

I'm a little unclear as to how courses fit in with your class structure. I assume that a class is a group of students who are at approximately the same level of understanding, but does that apply across many courses (math, science etc.)? If a student fails a test in a course do they fall back to the same course but in the previous class? What about the other courses they are taking--are they kept at the same class level for those courses for which they have not failed a test?

[gdpoc]

First with the courses. Basically we teach two courses, one of which is an orientation. The other course is taught after they pass the first course. A student will only be in one course at a time. First the orientation and then the other course. If a student fails and goes back a class he/she will go back to the first available class that is closest to their knowledge level.

Each class begins in the orientation course with a set number of students who will progress with each other. If someone fails then they will be sent to a new class while the original class continues on with the course.

I will build a junction table for classes. Originally I was just worried about maintaining continuity for which class they had been in first, then second, etc... but I just added a date entered field on the join table and I'll update that programatically when they roll a student back. Then I'll have it sorted chronologically.

But, back to the original question, do you know off the top of your head any way to solve this?

[jzwp11]

The short response to your original question is that yes it can be done in a form. That form would be based on a query. Getting that query depends on the correct table structure which is why that has been my focus.

Since you have 2 courses that every student in a class must take and every student has 2 courses, that is another many-to-many relationship.

tblStudentClasses
-pkStudentClassID primary key, autonumber
-fkStudentKey foreign key to student roster
-fkClassID foreign key to tblClasses

tblCourses
-pkCourseID primary key, autonumber
-txtCourseName

tblStudentClassCourses
-pkStudentClassCourseID primary key, autonumber
-fkStudentClassID foreign key to tblStudentClasses
-fkCourseID foreign key to tblCourses

Similarly a student (while in a class/course) can take many tests and a test can be taken by many students (many-to-many)

tblTests
-pkTestID primary key, autonumber
-txtTest
-Gradeable (logical/boolean (yes/no) field)

tblStudentClassCourseTests
-pkStuClassCourseTestID primary key, autonumber
-fkStudentClassCouseID foreign key to tblStudentClassCourses
-fkTestID foreign key to tblTests
-TestOrder
-dteTest (date of test)
-Score

I'm not sure where the Block field falls from your original TestStudent table.

[gdpoc]

Block would be like a semester. The first course has one block and the second has 5. Tests are organized by block.

[jzwp11]

OK then you have to add a table for the block between the course (tblStudentClassCourses) and test (tblStudentClassCourseTests) tables I proposed earlier and adjust the key fields accordingly.

[gdpoc]

I just went from three tables to eight. Give me a bit to wrap my mind around this.

[jzwp11]

Fair enough. Please post back when you are ready.

[gdpoc]

Ok. I did the overhaul and added the tables. I also did what I said I was going to do, i.e.
"I just added a date entered field on the join table and I'll update that programatically when they roll a student back. Then I'll have it sorted chronologically."

Now I have a slight issue. The only way I know to pull up the latest class that the student has been in is by using the last function in totals. If I use totals in a query however I can't add data via form.

I built a form that looks like such
View Classes Form
---------------------------------------------------------------------------------
[Class Key Seeker Control] [Students in class subform]



---------------------------------------------------------------------------------
It's a fairly simple form. The class key seeker is a combo box that pulls its values from the class list in the Classes table. The subform is linked to the control.
Is there a way that I can filter students out who have been rolled back from that class without using totals?

[jzwp11]

You are correct, when you use an aggregate query, you most likely will end up with a recordset that you cannot update, but you can use the key value in that form or subform to migrate the user to a form where edits can be made.

[gdpoc]

I think I've run into a slight quandry. I'm about to start working on the test side. Looking at the table structure you've proposed is there any way to associate test grades to an individual student? It appears that the tests are associated to the class number but are not able to be associated to a particular student.

[jzwp11]

This table will hold the grade/score:

tblStudentClassCourseTests
-pkStuClassCourseTestID primary key, autonumber
-fkStudentClassCouseID foreign key to tblStudentClassCourses
-fkTestID foreign key to tblTests
-TestOrder
-dteTest (date of test)
-Score

[gdpoc]

Ok, I believe I'm ready to create the form that I need and am having such difficulties with. I have my table structure set up like you suggested and I do believe I understand the data flow now. I have created most of the forms I need.

What should be the next area that I need to work on?

I haven't said it yet so I'll say it now. Thanks very much for your timeliness and help.