kindly assist me with a query to promote learners to the next grade and delete learners in 8th grade
kindly assist me with a query to promote learners to the next grade and delete learners in 8th grade
The learners are to be promoted to the next grade on every 1st January every year. Is there a code for this or a query to increment grade and delete records in 8th grade
First thing I'd recommend is to get rid of the table lookup for grade values. Put them in their own table and store the autonumber ID field of that table in your grade values. So if in this new table tblGrades, 2 represents kindergarten, you store 2 in the Grade field of Student table. It will then be far, far easier to increase 6 to 7 than it is to increase 6th to 7th as text values. See below - especially make sure you understand normalization so that you grasp how to use tblGrades values as foreign keys (fk) in Student table.
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/
Important for success:
Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837
What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm
The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
If you decide to follow that advice/direction, this sql would advance all students as per your post. Assumes you delete the lookup field and replace it with GradeFk and create tblGrades with fields GradeID and GradeDesc (where GradeDesc means GradeDescription)
However, it won't delete records. First, that would be a second operation (DELETE query) but perhaps more important, deleting records like this is not recommended. Better to have a Date field (e.g. ArchiveDate) in Students table to archive the records. You include this field in queries and recordsets to filter out or select students as required. Records with no archive date are 'current'.Code:UPDATE Students SET Students.GradeFK = [GradeFK]+1 WHERE (((Students.GradeFK)>2 And (Students.GradeFK)<10));
EDIT - forgot to say that if you have a lot of student records that would need to be set up you might need a query that will match your text value (e.g. 6th Grade) to the appropriate value from tblGrades. Will have to wait and see what you decide.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Your approach is working though I didn't understand GradeFK
Hi Can you now upload your amended version of the database?
You can PM me if you need further help.
Good Reading https://docs.microsoft.com/en-gb/off...on-description
Too long ago to recall everything about this. All I can gather at present is that you had a lookup field in a table and the suggestion was to replace it with GradeFk (Grade Foreign Key) so that you could relate the foreign key field as per the links above on how to relate tables. As below
You have pets of animal type 1,2,5,6 A query returns dog, etc. by joining AnimalFk to AnimalPK. You don't store "dog" in tblMyPets.
tblAnimals tblMyPets AnimalPK Animal PetIDPK AnimalFk 1 dog 1 1 2 cat 2 2 3 bird 3 5 4 fish 4 6 5 horse 6 goat 7 snake
If that's new to you, you either didn't grasp the normalization topics or didn't read them. Either way, you'd be wise to invest the time to learn db normalization if you're going to continue making db's.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
I have attached the database. I want to promote students from FORM 1 TO FORM 2 AND FROM FORM 2 TO FORM 3 AND FROM FORM 3 TO FORM 4. THOSE IN FORM 4 TO BE ARCHIVED. Also I am requesting for assistance to refer to previous saved marks for:
1. LAST TERM I.E IF YOU ARE IN TERM 2 YOU SHOULD BE ABLE TO SEE RESULTS FOR TERM 1.
2. LAST YEAR I.E IF YOU ARE IN 2022 YOU SHOULD BE ABLE TO SEE RESULTS FOR 2021.
Thanks in advance. god bless you so much
Hi
Currently your structure does not allow you to say which Year the Student has achieved any Grades?
You can PM me if you need further help.
Good Reading https://docs.microsoft.com/en-gb/off...on-description
Kindly adjust the database. I will really appreciate. I am still new in access
Hi
So a Student is in the same Stream for the whole time they attend School?
The normal School cycle is :-
in 1921 the Student starts in Kindergarden and would not normally have to pass an Exams ? Grades
In 1922 the Student would then move to Junior Class and would obtain a number of Grades for different Subjects.
1n 1923 the Student would then move to Senior Class and would obtain a number of Grades for different Subjects.
Is this what happens in your organisation?
If the process is different then please let us know.
You can PM me if you need further help.
Good Reading https://docs.microsoft.com/en-gb/off...on-description
Yessssss. A child is in the stream for the whole year. So it means a child can be in form 1s in the year 2021.
Also how does a Stream relate to the Student?
In the tblStreams you have North, East, & West
Is this just the area that the student is from?
You can PM me if you need further help.
Good Reading https://docs.microsoft.com/en-gb/off...on-description
Hi
Your relationships should be something like shown in the following screenshot.
If you can create the tables for this and then upload we can then help you further.
Last edited by mike60smart; 10-31-2021 at 03:10 PM. Reason: add screenshot
You can PM me if you need further help.
Good Reading https://docs.microsoft.com/en-gb/off...on-description
I have attached updated database. Thanks in advance for your assistance.