setting up a relational data base to enter my students & grades

[Guest]

Need help setting up relational database to store students info and grades.
I would like to enter student ID, Student name, Course #, etc only once into
the student table then have it connected threw student ID to a assingment
table that contains the sudents Marks for the many assignment and another
table that contains the test scores. I would also like to have some
caculated fields in the students table for for totals of assignment and test
marks then a final grade. It is alot and i am having many difficulties
getting started.

 

[Allen Browne]

This article describes the most basic aspects:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

A complete solution would need to grasp the concept that students do not
enrol in a subject, but in an instance of a subject (one subject is taught
many times over the years, and they enrol in one instance.)

The one-to-many relationships include:
- A certificate is has many units
- A unit is offered many times (different dates/coordinators)
- A student enrols in many units
- A unit has many classes (attendance register)
- A unit has many assignments
- A student may submit an assignment many times.

 

[Guest]

Thanks that points me in the right direction however got a few problems have
things set up like this

Students Table that contains
Student ID
Last Name
First Name

Ass Table that includes
Student ID
Ass ID
Ass Description

Mark Table that contains
Student ID
Ass ID
Grade

Have a one to many relation ship from Ass Table linking the fields Ass ID in
Ass table to the Mark table. Have another one to many relationship from
students table linking the Student ID to Student ID field in the Mark Table.
I have referential enforcement , and both cascade options turned on seeme
like a good idea.

I always get this error when tring to enter data directly into the Mark table

You cannot add or change a record because a related record is required in
the table students. Also kind of lost with the creation of forms think i
know what i want just not sure how to get there or if it is possible.

I would like a form that allows me to enter the grade based on the student
# and Ass # like in your example but am not sure how to do this. Please help

 

[Allen Browne]

1. Open the Mark Table in design view.

2. Select the [Student ID] field

3. In the lower pane remove the 0 from the Default Value,
and set the Required property to Yes.

4.Repeat steps 2 and 3 for the [Ass ID] field.

5. Save the table. Close.

That should solve the problem with the invalid number. If you do not supply
both the Student ID and the Ass ID, you will get an error saying the fields
are required, and you must enter both numbers.

If you are using combo boxes in your table (typically because you used that
evil Lookup Wizard), you may get other errors if the combos are not set up
correctly.

Once you have the tables working correctly, you can then create a main form
bound to the Students Table, with a subform bound to the Marks Table. The
subform will have a combo for the [Ass ID].