question about relation

[pavlos]

Hi,
I try to create a student administration database and need some help…

I use two tables, table STUDENTS and table GRADES. Table STUDENTS stores all the usual info (name, address, tel, email, etc). Table GRADES has about 10 fields, the first has the manes of the teaching units (about 10) and the rest fields are to be used for the marks.
I use a tabbed form with 2 pages to display the data for each student. The first page displays the student details (name, address, tel, etc) and the second page must display the table GRADES in order to put the marks. In other words I try to create a relation between ALL teaching units from table GRADES with each student and I got stuck! Any help will be appreciated!

Regards,
Pavlos

 

[MSAccessRookie]

Hi,
I try to create a student administration database and need some help…

I use two tables, table STUDENTS and table GRADES. Table STUDENTS stores all the usual info (name, address, tel, email, etc). Table GRADES has about 10 fields, the first has the manes of the teaching units (about 10) and the rest fields are to be used for the marks.
I use a tabbed form with 2 pages to display the data for each student. The first page displays the student details (name, address, tel, etc) and the second page must display the table GRADES in order to put the marks. In other words I try to create a relation between ALL teaching units from table GRADES with each student and I got stuck! Any help will be appreciated!

Regards,
Pavlos

I will assume for the sake of argument that any Student can participate in more than one Teaching Unit, and that any Teaching Unit can be attended by more than one student. This is what is known as a Many to Many relationship. In these cases, a third table is required to allow the Many sides to join properly.

Try something like:

tblStudents:

Table of Student related Information Including a Unique StudentID Field.
​

tblTeachingUnit:

Table of Teaching Unit related Information not including the grade, but Including a Unique TeachingUnitID Field.
​

tblGrades:

Table of Grade Related Information including the Grade, a StudentID (from tblStudents), and a TeachingUnitID (from tblTeachingUnit)
​

Note that the tables are not intended to be complete as described, and you will need to refine them as required.

 

[pavlos]

I have already tried the approach many to many, it does not give the expected results. It looks like i need a mechanism where each student is automatically related with all teaching units from table GRADES.

 

[MSAccessRookie]

I have already tried the approach many to many, it does not give the expected results. It looks like i need a mechanism where each student is automatically related with all teaching units from table GRADES.

Access does not normally process information in the way that you are describing. The Many/Many repationship that I described is a standard method of handling situations like this. Can you provide some additional information as to what you have tried so that we can assist you in moving toward an Access based solution?

 

[pavlos]

Many thanks for helping!

Using the usual many to many relationship approach the two IDs (StudentID and TeachingUnitID) fields of the third table must be filled in every time we want to relate the teaching units to a student. Thus, every time I open the second page not all teaching units are shown.

The basic idea of the database is after selecting a student the first page of the form to display the details of the student and the second page to display all fields of the table GRADES because every student attends all the teaching units.

Even if I tried using a query “as the third table” to join the tables STUDENT and GRADES but did not work…

Probably the solution is a matter of better normalization and I cannot finger it out!


Regards,
Pavlos

 

[pavlos]

Any help would be most welcome....

 

[Rabbie]

It looks like you are not using the Junction table(3rd Table) approach correctly. perhaps if you could post your DB then we could help you to get it right.

 

[CraigDolphin]

I agree. An example db might help. However, to step back a little:

The goal of this database is to record the grades of students, correct?

And all students take a range of classes?

So, we obviously require a table that lists Students and a table that lists Classes.
tblStudents
StudentID (auto, pk)
StudentFName
StudentLName
etc


tblClasses
ClassID (auto,pk)
ClassName
ClassTeacherID (fk) (which implies a teachers table too if you store this information)
ClassLocation
etc


Then you need a table to store the results of each class, for each student who takes that class.

tblStudentGrades
StudentGradeID (auto, pk)
StudentID (fk)
ClassID (fk)
StudentGrade_Score


Something along these lines is the type of structure you need to logically handle the kind of data you're working with.

Now, the next part is how to input the data.

The usual way to enter data into related tables is to use a form bound to a parent table (let's say we use a student-centric data entry approach, but this could also be done using a class-centric approach)

Your main form is bound to tblStudents (single form view)

You also need a subform on that main form which is bound to tblStudentGrades (continuous form view). The form/subform are linked using the StudentID field as the master/child fields. That way, whenever you create a record in the subform, the studentID value in tblStudentGrades is automatically filled in with the StudentID value of the current student being shown in the main form.

Now, at this point you could go through and manually add each class and score to the subform, for each student. However, to remove some of that data entry burden you could automate adding a record with a blank score for each class by means of running an append query which selects all the classes from tblClasses and adds a line for each to tblStudentGrades making sure to explictly populate the StudentID field with the current value in the main form. You might want to restrict this process to only adding classes for that student that are not already in the grades table.

If you go the automation route, you need to decide how best you want to trigger running of this append query (command button or some other event?). And you will need to be sure to ensure that all required information is available on the main form before allowing the code to run.

 

[pavlos]

The db is not in English, however, I created the attached sample in English that shows the main idea.

 

[pavlos]

The db is not in English, however, I created the attached small sample in English that shows the main idea.

I cannot upload the file, I get the message invalid file...

 

[pavlos]

Ok, here is the file attached...

Many thanks for helping guys!

 

[pavlos]

.... Now, at this point you could go through and manually add each class and score to the subform, for each student. However, to remove some of that data entry burden you could automate adding a record with a blank score for each class by means of running an append query which selects all the classes from tblClasses and adds a line for each to tblStudentGrades making sure to explictly populate the StudentID field with the current value in the main form. You might want to restrict this process to only adding classes for that student that are not already in the grades table...

Craig thank you very much for helping! Also, I apologize for my late response... I was away from my work for a few days.

Your reply helped me to realize better the structure of the base as well as the relations of the tables. All these days I was trying to create a relation that will automatically adds the teaching records and now I realized that there is not such a relation and this can only be achieved by using an append query.

I have already achieved part of what I was I am trying to do, however, there are still a few questions that I will try to resolve alone. If not then I will call your help again!

Many thanks!
pavlos