Assign Courses to students so only those courses show up in forms and queries w/Student name

[MichaelA]

In my grade book, I'd like to assign courses to individual students so that when I query MissingAssignments and filter it for one student it only returns the assignments for the courses that student is registered for. Also, in the form Grades, I have cascading combo boxes in CourseID and AssignmentID, so when I select a course in Course ID, the combo box Assignments requeries and shows only the Assignments for that course. I would like to limit the CourseID selection to only those courses the student is registered for.

I've attached the latest zip file of the database.

Thank you for all of your help!

[June7]

You need a table that relates students to assignments. I don't even see a table that relates students to courses, other than Grades table. If you don't create record in that table until student has a grade, then where would you identify all courses/assignments student is registered for?

You can create records in Grades table when student registers then add grade value later.

Need to resolve this before addressing your other requirements.

There is a duplicate record in Grades and need to delete one of these unless you want to allow student to have the same assignment more than once in same year:

ID	StudentID	SchoolYearID	AssignmentID
1561	Phoebe	11	Read Part 3: The Law of Your Mother and Answer Discussion Questions
1562	Phoebe	11	Read Part 3: The Law of Your Mother and Answer Discussion Questions

Then set a unique compound index on StudentID, AssignmentID, SchoolYearID to prevent this duplication. If student can have an assignment only once ever, then don't include year in this index (in which case, what purpose does year serve in Grades?).

Not necessary to save CourseID into Grades because it can be retrieved from Assignments table.

There are 2 records in Assignments with no assignment description.

Advise not to build lookups in table (I never do).

[MichaelA]

The Grades table is the only place they are related via the Grades Form. The lookups, I think, were created when I made the form. Other than that, I don't think I created any lookups. How do I get rid of those without it affecting the Grades For?

If a student is registered for a course, then they would be responsible for all assignments in that course.

I deleted the duplicate record. No, I don't want the student to have the same assignment more than once. And I deleted the Assignments with no title. Thank you!

I still have the same question, though. How would I register students to courses. What would that table look like, how would it related to the other tables.

I'm sorry but I am an extreme notice.

Thank you, so much, for your help.

[ssanfu]

Also, the top two lines in EVERY module should be

Option Compare Database
Option Explicit

If you go to IDE -> Tools -> Options
In the tab "Editor" the second check box is "Require Variable Declaration". This should be checked. For any NEW module, "Option Explicit" will automatically be added.


I modified your Relationships to how I would design your table structures:

Note the table name prefixes and the PK/FK suffixes for the fields.
I also removed the look-ups in the "Grades" table.



Just curious - did you design the tables\relationships using pencil and paper or just jump into Access? It is a lot easier to modify the initial design on a white board than in the computer.

I don't know how detailed you want this dB to be, but it seems to me that there might be a few fields that are missing.
You have a field for "Grade". That looks like the grade for the school year.
What about a grade for each assignment?
What about a grade for each course?
Do you need\want a grade by semester/trimester?
Maybe some date fields might be needed....


Additionally it looks to me like that are several junction tables missing. I would probably have a main form/sub form design.



My $0.02 worth.....

[June7]

The Grades table is the only place they are related via the Grades Form. The lookups, I think, were created when I made the form. Other than that, I don't think I created any lookups. How do I get rid of those without it affecting the Grades For?

If a student is registered for a course, then they would be responsible for all assignments in that course.

I deleted the duplicate record. No, I don't want the student to have the same assignment more than once. And I deleted the Assignments with no title. Thank you!

I still have the same question, though. How would I register students to courses. What would that table look like, how would it related to the other tables.

Open table in design, select field, change combobox to textbox on Lookup tab. This will not affect the form.

Apparently, I edited my previous post after you read it, review again.

[MichaelA]

Did you actually do this in Access? If so could I get a copy?

[MichaelA]

[QUOTE=June7;439979]You need a table that relates students to assignments. I don't even see a table that relates students to courses, other than Grades table. If you don't create record in that table until student has a grade, then where would you identify all courses/assignments student is registered for? This was my question.

You can create records in Grades table when student registers then add grade value later. I don't understand

Need to resolve this before addressing your other requirements.

There is a duplicate record in Grades and need to delete one of these unless you want to allow student to have the same assignment more than once in same year:

ID	StudentID	SchoolYearID	AssignmentID
1561	Phoebe	11	Read Part 3: The Law of Your Mother and Answer Discussion Questions
1562	Phoebe	11	Read Part 3: The Law of Your Mother and Answer Discussion Questions

Then set a unique compound index on StudentID, AssignmentID, SchoolYearID to prevent this duplication. If student can have an assignment only once ever, then don't include year in this index (in which case, what purpose does year serve in Grades?). It serves to tell what school year they took the course.

Not necessary to save CourseID into Grades because it can be retrieved from Assignments table.

There are 2 records in Assignments with no assignment description.

Advise not to build lookups in table (I never do).

[ssanfu]

Did you actually do this in Access? If so could I get a copy?

You don't say who this is directed to, so I'm going to assume you meant me.

This is ONLY the tables\relationships.
Enjoy...??

[MichaelA]

What is a compound index and where do I set it?

[June7]

To assign composite/compound index: http://www.geeksengine.com/article/c...ex-access.html

Grades is the only table that shows association of student with course/assignment. Create these records when student record is created (when student is 'registered'). VBA code can automate this. But one step at a time. Figure out the compound index first.

Students will have multi-year data? Can student have same assignment in more than one year?

[ssanfu]

Also see
YouTube: https://www.youtube.com/watch?v=uKwLIvV_S9Q

[MichaelA]

To assign composite/compound index: http://www.geeksengine.com/article/c...ex-access.html

Grades is the only table that shows association of student with course/assignment. Create these records when student record is created (when student is 'registered'). VBA code can automate this. But one step at a time. Figure out the compound index first.

Students will have multi-year data? Can student have same assignment in more than one year?

adeYes, student's will have multi-year data. 9th grade, 10th grade, 11th grade, 12th grade. The only way that can have the same assignment in more than 1 year is if the fail the course and have to take it again.

[MichaelA]

I don't know how detailed you want this dB to be, but it seems to me that there might be a few fields that are missing.
You have a field for "Grade". That looks like the grade for the school year. The only grade field is grades for assignments.
What about a grade for each assignment?
What about a grade for each course? Grade for the course is calculated as an avg. of all assignment courses.
Do you need\want a grade by semester/trimester? All courses are one full year.
Maybe some date fields might be needed....


Additionally it looks to me like that are several junction tables missing. I would probably have a main form/sub form design. ???????



My $0.02 worth.....

Thank you! Your help is greatly appreciated.

[ssanfu]

"Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.

Additionally it looks to me like that are several junction tables missing

A junction table is how a many-to-many relationship is implemented. An example of a many-to-many relationship would be:
One student can attend many years. (One-to-many relationship) and
One year can have many students (One-to-many relationship)

One way to look at the tables


another way to look at it


Both depict a many-to-many relationship.


You should read these. This concept is very important:

What is Normalization?
=======================
What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html
What Is Normalization, Part II: Break it up. http://rogersaccessblog.blogspot.com...n-part-ii.html
What Is Normalization: Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
What is Normalization: Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
What Is Normalization: Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html




Work through these tutorials - take the time to actually do them.
Tutorials
--------------
http://www.rogersaccesslibrary.com/forum/forum46.html


------------------------------------------------------------------------------------------------------------------------------------------
I was looking at your design again. What do you think of this? (Notice the table name changes)

[MichaelA]

I'm at work, so I can't look to deeply into this now but I have one quick question. If I start renaming my tables and fields, will date be lost?