Solved Education Students and Classes (1 Viewer)

IMyself

Registered User.
Local time
Today, 15:23
Joined
Oct 15, 2019
Messages
30
Hi, I was making a database for an educational institute, which has classes for each level of education.
There are 3 tables engaged together: Schools working with this institute, Classes holding in the schools, and the Students participating in classes.
the schools and classes are unique, and each new class gets a new id, but the students may appear in some classes.
all the classes' subjects are the same, but their level are difference, and each student start with a Level A class,and then goes to Level B class and so on.
after making relationships, in school table left side of its id field, there's a plus sign which expands the related classes table, and in the expanded table also, there's a plus sign next to class id which will expand the related students table.
the problem is that one student from the student's table cannot appear in more than one class because of relationship and manner of ids.
so is there any method to solve this problem in its way ? or it should be changed to another way ?
I did attach an example access file by the way.
Thanks A lot !
 

Attachments

  • EduTest.zip
    34.3 KB · Views: 215

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:53
Joined
May 21, 2018
Messages
8,463
You have a many to many relationship. To do that you need a junction table

tblStudents_Classes
StudentID_FK ' foreign key to the student table
ClassID_FK 'foreign key to class table

assume
student 1 in classes a,b,c,d
student 2 in classes a,b,e,f
student 3 in classes a,c,e,f

as you can see many students may be in the same class, and a student can have many classes. Your data is
1 a
1 b
1 c
1 d
2 a
2 b
2 e
2 f
3 a
3 c
3 e
3 f

you need to get rid of all those class fields and relations.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:53
Joined
May 21, 2018
Messages
8,463
Also your classes table, looks like you are trying to create a junction table, if so you are missing a pure class table.
I would assume this is also a many to many, but I am not sure what kind of details you store with classes. I would think I have
tblSchools
tblClasses
tblStudents
tblSchool_Classes
tblClasses_Students

If each school can have classes like Biology 101, Earth Science, Algebra 1 then they all go in the class table
Then I would have in the School_Classes table
1 AA
1 BB
1 CC
2 AA
2 BB
2 EE

School 1 offers, AA, BB, CC and School 2 offers AA, BB, EE
In this junction table you can have other fields unique to that relation between that school and that class.

Any chance this is a homework assignment? kind of smells like one.

Building a many to many interface gets a little challenging if you never seen it before, but once you see it not difficult. Whatever you do, DO NOT LOOK AT MULTI VALUE FIELDS, to solve this.
 

IMyself

Registered User.
Local time
Today, 15:23
Joined
Oct 15, 2019
Messages
30
Thank you so much 🙏
I think this is somehow complicated and confusing, and no, it's not homework :)) but I am intermediate in access.
The institute teaches only abacus mental mathematic in other Schools in after school, and this is the one and only course. But this course has 6 levels and each students that begin to learn, should start from Level one and continue...
The matter is that each class shall differ and is not the same as usual School courses.
The time, the teacher or other parameters may change and the institute wants to log all these separately.
So the Schools table has the information of each School, and the Classes table has the information of each class, and students table have its infos.
 

IMyself

Registered User.
Local time
Today, 15:23
Joined
Oct 15, 2019
Messages
30
And I'm very delightful about it, I didn't know about junction table and I guess this is the best solution I could get.
But the only thing is that the students name doesn't show up under classes. Is there any chance I can see the students name
and last name next to the SIDs ?

Acc.jpg
 

GinaWhipp

AWF VIP
Local time
Today, 06:53
Joined
Jun 21, 2011
Messages
5,901
Well, I don't ever view data in the table and never use subdatasheets. That said, you should, at the very least, use a query to which you can add the Students table and drop the last name in it.

Side note, tables are for storing data, for display use Forms. Not only can you control what gets entered into your table but you ensure a mistake isn't made and you mess something up.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:53
Joined
May 21, 2018
Messages
8,463
@IMyself
Here is a quick demo. You can add, edit and assign from this. I personally would break up the adding of schools and classes to their own forms, because this may be a little confusing being able to do all of this. If interested in this I will talk you through how it was done. Pretty simple but if never done it before it will be confusing. What I did not do, but can be added is to remove names from the list. So if John Brown is added to a class it removes him from the pull down. If interested I can add but you probably want to see the easy version first.
StudentClasses.jpg
 

Attachments

  • MajP_EDU.zip
    35.8 KB · Views: 216

IMyself

Registered User.
Local time
Today, 15:23
Joined
Oct 15, 2019
Messages
30
Well, I don't ever view data in the table and never use subdatasheets. That said, you should, at the very least, use a query to which you can add the Students table and drop the last name in it.

Side note, tables are for storing data, for display use Forms. Not only can you control what gets entered into your table but you ensure a mistake isn't made and you mess something up.
Yes I should do that indeed.
Many Thanks 👍
 

IMyself

Registered User.
Local time
Today, 15:23
Joined
Oct 15, 2019
Messages
30
@IMyself
Here is a quick demo. You can add, edit and assign from this. I personally would break up the adding of schools and classes to their own forms, because this may be a little confusing being able to do all of this. If interested in this I will talk you through how it was done. Pretty simple but if never done it before it will be confusing. What I did not do, but can be added is to remove names from the list. So if John Brown is added to a class it removes him from the pull down. If interested I can add but you probably want to see the easy version first. View attachment 79159
Wow gorgeous job. Thank you very much for taking time and making this. I Really appreciate it 🙏
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:53
Joined
May 21, 2018
Messages
8,463
There are a few tricks.
1) The first subform is a standard subform linked to the main form. If you do not know how to use the LinkMasterFields and LinkChildFields properties google how
2) The second subform is linked to a hidden textbox. You can see it in design view and you may want to unhide it to understand how it works.
3) The first subform is setting the value of the hidden textbox to the selected classID. See the code in the subform.
4) I realized my combo is FirstName, LastName should have been the opposite way around. You can look at the rowsource of the combobox to see and fix the concatenation and the formatting to show the name but hide the studentID and save the studentID.

When I have a many to many I like to have a form to show the opposite order as well. So the opposite would be
School (Main form)
Student (1st sub)
Student Classes (2nd sub)

That way you could select a student and see their classes and assign a new class. Depends on how you will do your data entry. Sometimes you have a class and you want to fill it, other times you would like to fill out a students schedule or see what they have completed.
 

IMyself

Registered User.
Local time
Today, 15:23
Joined
Oct 15, 2019
Messages
30
There are a few tricks.
1) The first subform is a standard subform linked to the main form. If you do not know how to use the LinkMasterFields and LinkChildFields properties google how
2) The second subform is linked to a hidden textbox. You can see it in design view and you may want to unhide it to understand how it works.
3) The first subform is setting the value of the hidden textbox to the selected classID. See the code in the subform.
4) I realized my combo is FirstName, LastName should have been the opposite way around. You can look at the rowsource of the combobox to see and fix the concatenation and the formatting to show the name but hide the studentID and save the studentID.

When I have a many to many I like to have a form to show the opposite order as well. So the opposite would be
School (Main form)
Student (1st sub)
Student Classes (2nd sub)

That way you could select a student and see their classes and assign a new class. Depends on how you will do your data entry. Sometimes you have a class and you want to fill it, other times you would like to fill out a students schedule or see what they have completed.
Perfect at all! Many thanks dear
 

IMyself

Registered User.
Local time
Today, 15:23
Joined
Oct 15, 2019
Messages
30
There are a few tricks.
1) The first subform is a standard subform linked to the main form. If you do not know how to use the LinkMasterFields and LinkChildFields properties google how
2) The second subform is linked to a hidden textbox. You can see it in design view and you may want to unhide it to understand how it works.
3) The first subform is setting the value of the hidden textbox to the selected classID. See the code in the subform.
4) I realized my combo is FirstName, LastName should have been the opposite way around. You can look at the rowsource of the combobox to see and fix the concatenation and the formatting to show the name but hide the studentID and save the studentID.

When I have a many to many I like to have a form to show the opposite order as well. So the opposite would be
School (Main form)
Student (1st sub)
Student Classes (2nd sub)

That way you could select a student and see their classes and assign a new class. Depends on how you will do your data entry. Sometimes you have a class and you want to fill it, other times you would like to fill out a students schedule or see what they have completed.
Hi Again. I did everything step by step, and there I see the students combo box doesn't show the name, only the ID
Untitleds.jpg

I also used the same parameters in the Row Source of Your file
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:53
Joined
May 21, 2018
Messages
8,463
If your query returns two fields an ID, and Name Field and you want to show the name and hide the ID
BoundField = 1
column count = 2
column widths = "0";"2"
where 2 is how many inches

this way you have 2 columns but hiding the first and bound to the first.
 

IMyself

Registered User.
Local time
Today, 15:23
Joined
Oct 15, 2019
Messages
30
If your query returns two fields an ID, and Name Field and you want to show the name and hide the ID
BoundField = 1
column count = 2
column widths = "0";"2"
where 2 is how many inches

this way you have 2 columns but hiding the first and bound to the first.
No, the combo box only shows the first field. I made a combo box next to yours.
Untitleds.jpg

The student is what you made and Name is mine. it only shows the full name when its clicked.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:53
Joined
May 21, 2018
Messages
8,463
A combo box can show multiple fields in the pulldown, but only displays the bound column. That is just how it works. I did a concatenation to get around that. If you like multiple fields shown you have to use a listbox.
 

Users who are viewing this thread

Top Bottom