Solved Yearss (1 Viewer)

[mike60smart]

I guess what is left is now me, myself and I alone. Thanks

Hi Mercy

Can you give me a list of the actual Grade names rather than using Grade 1 - 6

 

[Mercy Mercy]

GRADES- STANDARD ONE.
STANDARD TWO.
STANDARD THREE.
STANDARD FOUR
STANDARD FIVE.
STANDARD SIX

 

[mike60smart]

GRADES- STANDARD ONE.
STANDARD TWO.
STANDARD THREE.
STANDARD FOUR
STANDARD FIVE.
STANDARD SIX

I thought you said previously that grades equate to Kindergarden, Primary , Secondary??

 

[Mercy Mercy]

Kindergarten- PP1.
PP2.
PRIMARY- GRADE 1
GRADE 2
GRADE 3
GRADE 4
GRADE 5
GRADE 6
SECONDARY- GRADE 7
GRADE 8
GRADE 9

 

[mike60smart]

HI mercy

See if this is what you are after. It is very rough.

 

[Mercy Mercy]

HI mercy

See if this is what you are after. It is very rough.

It works but how do you relate Examination type like
EXAMINATION TYPE.
ASSESSMENT 1
ASSESSMENT 2
ASSESSMENT 3
ALL I NEED IS REFERENTIAL INTEGRITY.

Thanks for going an extra mile. I appreciate.

 

[mike60smart]

It works but how do you relate Examination type like
EXAMINATION TYPE.
ASSESSMENT 1
ASSESSMENT 2
ASSESSMENT 3
ALL I NEED IS REFERENTIAL INTEGRITY.

Thanks for going an extra mile. I appreciate.

In Each Term you have a specific set of Subjects.

What is your process of Students sitting Examinations?

 

[Mercy Mercy]

All students taking those subjects are subjected to the same exams every week.

@mike60smart I have liked your database. Exactly what I needed. Now I am trying to assign the students the dormitory to sleep in. The dormitory should have a teacher being in charge. Just kindly assist me with the relationship.

Your database works fine

 

[mike60smart]

All students taking those subjects are subjected to the same exams every week.

@mike60smart I have liked your database. Exactly what I needed. Now I am trying to assign the students the dormitory to sleep in. The dormitory should have a teacher being in charge. Just kindly assist me with the relationship.

Your database works fine

How many Students are allowed in each Dormitory?

 

[Mercy Mercy]

Well we have the following dormitories
LION. - 50 STUDENTS
HYENA. - 56 STUDENTS
ELEPHANT. - 60 STUDENTS
GAZELLE - 45 STUDENTS

 

[mike60smart]

Well we have the following dormitories
LION. - 50 STUDENTS
HYENA. - 56 STUDENTS
ELEPHANT. - 60 STUDENTS
GAZELLE - 45 STUDENTS

Which are Female and which are Male Dormitories

 

[Mercy Mercy]

LION- MALE.
HYENA- MALE

ELEPHANT - FEMALE.
GAZELLE- FEMALE

 

[Pat Hartman]

@mike60smart I guess you didn't read my comment about the schema. Your solution is incorrect. Either YearTermID, GradeID, and StreamID belong in tblStudents (which I don't recommend) OR you remove StudentID from tblTermStreams and make a junction table so that the termstream can have MULTIPLE students, not just ONE. You also have no connection between Students and TermSubjects. Plus, I'm not sure it shouldn't be Stream subjects instead. I don't know if you asked that question. In any event, Mark doesn't belong in tblTermSubjects (or tblTermStreamsSubjects if that turns out to be where subjects are connected). You need anther junction table to connect students to subjects and that's where the examination types connects, so we have another three column junction. Subjects, Examination, and Student with ancillary data for the grade the student achieved.

And finally, the frmYears doesn't have any validation so it allows incomplete/invalid records to be saved.

 

[Mercy Mercy]

@mike60smart I guess you didn't read my comment about the schema. Your solution is incorrect. Either YearTermID, GradeID, and StreamID belong in tblStudents (which I don't recommend) OR you remove StudentID from tblTermStreams and make a junction table so that the termstream can have MULTIPLE students, not just ONE. You also have no connection between Students and TermSubjects. Plus, I'm not sure it shouldn't be Stream subjects instead. I don't know if you asked that question. In any event, Mark doesn't belong in tblTermSubjects (or tblTermStreamsSubjects if that turns out to be where subjects are connected). You need anther junction table to connect students to subjects and that's where the examination types connects, so we have another three column junction. Subjects, Examination, and Student with ancillary data for the grade the student achieved.

And finally, the frmYears doesn't have any validation so it allows incomplete/invalid records to be saved.

Kindly I beg you to assist me with your sample database. Thanks in advance. I am really really really really begging you. Please. It is a humble request.

 

[Pat Hartman]

Mike did a great deal of work for you. We are not here to do your work for you. We are here to help you to help yourself. If you don't understand any of what I said, you might want to do some searching and read about database normalization and many-many relationships which you have.

tblTermStreams defines a stream. tblStudents defines a student. You need a junction table to connect a student to tblTermStreams if there is a m-m. OR, if a student belongs to a single stream, then on reflection, you should remove studentID from tblStreams and move tblStreamsID to the student table. But having StudentID in tblTermStreams is definitely wrong.

Here's a sample that shows how a m-m relationship works. You can start with that.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[Mercy Mercy]

Mike did a great deal of work for you. We are not here to do your work for you. We are here to help you to help yourself. If you don't understand any of what I said, you might want to do some searching and read about database normalization and many-many relationships which you have.

tblTermStreams defines a stream. tblStudents defines a student. You need a junction table to connect a student to tblTermStreams if there is a m-m. OR, if a student belongs to a single stream, then on reflection, you should remove studentID from tblStreams and move tblStreamsID to the student table. But having StudentID in tblTermStreams is definitely wrong.

Here's a sample that shows how a m-m relationship works. You can start with that.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

Thank God we have @mike60smart and @arnelgp in this forum. They are doing good work and many people appreciate their efforts. If you really wanted to help me you would have screenshot your schema.!!!!

 

[Pat Hartman]

It isn't MY schema. It is YOUR schema and how hard is removing a column from one table and adding a column to a different table? The relationship between the two tables is backwards. I even used YOUR column and table names. Come on now, really. Me making that change doesn't teach you anything. It simply makes you dependent on us doing your work for you. You need to put some effort into this. I also gave you an opportunity to clarify your thinking and specification since the schema is different if a student can belong to more than one stream. So, now I have to make TWO pictures and a picture isn't going to help you. You need to look at your data and see which schema is correct for your situation.

As long as @mike60smart and @arnelgp feel the need to build things for you when you won't put out effort or clarify your intent, they are welcome to you

 

[Mercy Mercy]

It isn't MY schema. It is YOUR schema and how hard is removing a column from one table and adding a column to a different table? The relationship between the two tables is backwards. I even used YOUR column and table names. Come on now, really. Me making that change doesn't teach you anything. It simply makes you dependent on us doing your work for you. You need to put some effort into this. I also gave you an opportunity to clarify your thinking and specification since the schema is different if a student can belong to more than one stream. So, now I have to make TWO pictures and a picture isn't going to help you. You need to look at your data and see which schema is correct for your situation.

As long as @mike60smart and @arnelgp feel the need to build things for you when you won't put out effort or clarify your intent, they are welcome to you

@Pat Hartman I used @mike60smart database by slightly changing his idea by creating the main form- from tblstudents. With that I have several subforms:-
1. tbltermstreams.
2. tblyearterms.
3. tbltermsubjects.

In tbltermsubjects I am able to use teacherID to show a certain teacher teaches a certain subject based on subjectID.

I was waiting for @mike60smart to assist me create referential integrity between subjectID and ExaminationtypeID and also assist me create referential integrity between students and dormitory and teachers. He was doing good work before you rudely interrupted him with your new referential integrities theoretically and with no practicality!!!!!! If only you could screenshot your referential integrities, it could be of great importance.
In any case I was able to create queries from @mike60smart idea and it worked. I wanted to use your idea of having another junctions especially on putting mark on a new junction called tblexaminationsubjects but there is an issue in relationship. I was not asking so much from you. Only you assist me with data relationships practically.

Though I am still new in access I am not that stupid. At least I am able to create forms and reports and macros and modules.
I only have a problem with referential integrities.!!!!!!!!

Thanks @mike60smart and @arnelgp

 

[Pat Hartman]

He was doing good work before you rudely interrupted him with your new referential integrities theoretically and with no practicality!!!!!!

You're pretty new here. Yes mike was helping you. I'm pretty sure he didn't stop helping you. I'm sure he'll be back You may not realize that none of us gets paid to help you and we don't spend 24/7 sitting here waiting for you to ask a new question. We have lives and we have work that we actually get paid to do so most of us don't let our free contributions interfere with that. Sometimes, you might have to wait a day or two for a response.

As a beginner, it is very helpful to attempt to help someone else. Mike is teaching himself Access by helping others. It is also kind and generous and I would never want to discourage him from using problems such as yours as a learning experience. But, the design was flawed and I pointed that out. Maybe you think that it is rude to point out errors but maybe mike appreciated it. It wasn't an opinion I was offering, it was a declarative statement. If I say "this is wrong", there is an excellent chance that I am correct. But, I didn't know exactly what was right since I am not omniscient, only that what you had at that point was wrong. How did I know that - EXPERIENCE! Years of it. It wasn't clear to me exactly what was right because I don't know what your specific requirements are or I would have been more forthcoming with a clear solution. I did offer THREE possibilities. But, instead of reading what I wrote and taking it at face value and trying to clarify, you elected to criticize me for not providing a written solution and what was really upsetting to you was that I didn't respond fast enough

Though I am still new in access I am not that stupid. At least I am able to create forms and reports and macros and modules.
I only have a problem with referential integrities.!!!!!!!!

Sometimes we just don't know what we don't know

 

[mike60smart]

@Pat Hartman I used @mike60smart database by slightly changing his idea by creating the main form- from tblstudents. With that I have several subforms:-
1. tbltermstreams.
2. tblyearterms.
3. tbltermsubjects.

In tbltermsubjects I am able to use teacherID to show a certain teacher teaches a certain subject based on subjectID.

I was waiting for @mike60smart to assist me create referential integrity between subjectID and ExaminationtypeID and also assist me create referential integrity between students and dormitory and teachers. He was doing good work before you rudely interrupted him with your new referential integrities theoretically and with no practicality!!!!!! If only you could screenshot your referential integrities, it could be of great importance.
In any case I was able to create queries from @mike60smart idea and it worked. I wanted to use your idea of having another junctions especially on putting mark on a new junction called tblexaminationsubjects but there is an issue in relationship. I was not asking so much from you. Only you assist me with data relationships practically.

Though I am still new in access I am not that stupid. At least I am able to create forms and reports and macros and modules.
I only have a problem with referential integrities.!!!!!!!!

Thanks @mike60smart and @arnelgp

As Pat says we all try to help each other. I don't mind being told when I am wrong as I am still learning at 77 years old.

The attached is a redesigned Structure which may still be wrong.
You can select the Year
Then select the Required Term
Select or enter a Student
Then select the Subject
Last but not least Enter Weeks & Examination Types.

Let us know if this covers your process or if we are way off the mark.