Access query to promote students to next class

[erickemboi]

kindly assist me with a query to promote learners to the next grade and delete learners in 8th grade

[erickemboi]

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

[Micron]

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

[Micron]

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)

UPDATE Students SET Students.GradeFK = [GradeFK]+1
WHERE (((Students.GradeFK)>2 And (Students.GradeFK)<10));

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

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.

[erickemboi]

Your approach is working though I didn't understand GradeFK

[mike60smart]

Hi Can you now upload your amended version of the database?

[Micron]

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

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

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

[erickemboi]

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

[mike60smart]

Hi

Currently your structure does not allow you to say which Year the Student has achieved any Grades?

[erickemboi]

Kindly adjust the database. I will really appreciate. I am still new in access

[mike60smart]

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.

[erickemboi]

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.

[mike60smart]

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?

[mike60smart]

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.

[erickemboi]

I have attached updated database. Thanks in advance for your assistance.