Solved Yearss

[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

 

[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.

 

[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.!!!!

 

[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

 

[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.

 

[Mercy Mercy]

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.

Thanks @mike60smart I appreciate

 

[Mercy Mercy]

I made a lot of nit-picky changes to the schema but you should also check each table because I may have missed stuff.
1. I removed 0 as the default for FK's. 0 is never valid so the default should be null
2. I changed the setting of the AllowZLS from yes to no for all the fields involved in the little lookup tables. I did not fix the teachers table or the other fields in the student table. In general, I ALWAYS change the AllowZLS property to No because I prefer that fields that are empty be null. That way I can always test for null and not have to worry about what data type a field is. Remember, dates and numbers are NOT STRINGS and so the AllowZLS doesn't apply to them since they can never contain a ZLS. It also prevents the problem with fields like LastName. If you have AllowZLS set to yes, the user can enter a blank name and even if the field is defined as required, Access will allow it which makes no sense. So, always change this property to No just on GP (general principles)
3. I removed all the extraneous indexes on the FK fields. When you create a relationship using the relationship window, Access automagically creates a HIDDEN index on the FK fields so you do not need to and should not create a second one.
4. The relationship between TermStreams and Students was backwards so I removed the StudentID from TermStreams and added the FK to TermStreams into Students
5. I renamed the Students table for consistency. Consistency is your friend. It will save you lots of hair pulling and head banging.
6, I added unique indexes to the text fields on the lookup tables. I don't like the way this is broken down but since it is not technically wrong, I didn't change it. I just fixed it to work correctly. I also made these fields required. So on tables like Years, Terms, Grades, etc. there is a unique index on the "text" field AND the "text" field is marked as required. It makes absolutely NO SENSE to have a row in one of these tables if it doesn't have a "text" field value. It also makes no sense for the "text" field in any of these tables to be duplicated so I added the unique index.
7. I added a new column to the Years table and named it Prior. If you will look at the frmStudents, you will understand why. I added a combo box so that the TermStreamID could be selected. Look at the RowSource query and you will see WHY I dislike this structure. But in any event, I made a concatenated name to make it easy for someone to pick the right TermStream. The query sorts the prior years to the bottom of the list but leaves them there and out of the way. I also added code to the Form's BeforeUpdate event to prevent a user from picking a TermStream for a prior year.
8. I also cleaned up the related tables of Term Streams. GradeStream existed but wasn't being used correctly so I used it correctly. I don't like it but again, it isn't wrong to have this as an intermediary table.


1, 2, 3, 5, 6 are things you need to learn to do by rote. You NEVER want to deviate. I have code I can run to clean up Access' default mess after I create a table. It is in one of my examples so I'll include a link to it. But the code cannot clean up your sloppy choices regarding allowing duplicates and not using indexes correctly. That takes discipline and some time to make it second nature so you don't even think about it.

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

Things I didn't fix
1. The frmYears and it's subforms. There was no point in doing that if I didn't fix the schema correctly.
2. I didn't fix all the poorly formed names. Names should contain ONLY letters, numbers, and the underscore. No embedded spaces, no special characters and also NO VBA function or property names. The link above includes a form that shows some of what bad things happen when you include non-standard characters in object names. Access INMNSHO, is way too lax regarding table, column, and object names. It is this laxness that causes you more work later. Access does it to be "friendly" to new users but in reality, this "friendliness" just causes problems down the line when you start writing code and queries.

Things I recommend that you change.
1. The attachment data type is one of those abomination fields that can't be upsized. I assume you are using them for photos of students and teachers. Unless you are planning on storing multiple images, I would just get rid of the attachment data type and save yourself some later agnst. Just use the OLE object type instead to hold ONE image. Better still, use a text field and store a link to the image. That will save a lot of database bloat that will start to affect the database after a few years of use. If you are storing multiple indexes, just make a child table and put the images in that so you can support multiples. Change your form to use a subform to show the child table with the OLE objects rather than using the attachment control. You get exactly the same functionality without having to learn a new variant of SQL or having a problem should you ever want to upsize the BE to sQL Server. And it is no more work if you do it from the start.

@Pat Hartman I apologize from the heart. I am really sorry for my previous comments. I am very very very very very very sorry. I hope I am forgiven. I have realized that you had no ill intentions. Thanks @Pat Hartman for going an extra mile assisting me with a sample database. I really really really really really really appreciate.

Once again thank you @Pat Hartman

Truce

 

[mike60smart]

This is Pats database modified slightly to allow the following:

1. Select a specific Year
2. Select a specific Term
3. Select a specific Grade / Stream
4. Allows the display of a Number of Students for the Grade/Stream selected
5. On selection of a Student be able to specify the Subject and then add all relevant Weeks, Exam Types & Marks.

I would imagine there are errors.

 

[Mercy Mercy]

This is Pats database modified slightly to allow the following:

1. Select a specific Year
2. Select a specific Term
3. Select a specific Grade / Stream
4. Allows the display of a Number of Students for the Grade/Stream selected
5. On selection of a Student be able to specify the Subject and then add all relevant Weeks, Exam Types & Marks.

I would imagine there are errors.

Thanks so much. I appreciate. I had also modified and it is working fine. Thanks @mike60smart and @Pat Hartman I really really really appreciate

 

[mike60smart]

Can we see your modified version?

There are still things wrong which need to be resolved.