Query to promote students

[Mercy Mercy]

Thanks for your help. My database is working fine. There are tables for school fees, Examinations and timetables I didn't deem it necessary to include in the query to promote students. It is working very well. If only I could be assisted in an Append Query to promote students, then I will be very grateful. Thanks in advance.

 

[Mercy Mercy]

The Streams in this case are,:

1. Single implying one single streamed class.
2. North
3. South.
4. Green
5. West.
6. East.

If for example a class like GRADE 1 has several classes holding several students, then the classes can be captured as follows

Grade. Stream.
Grade 1 North
Grade 1 South.
Grade 1 Green.
Grade 1 West
Grade 1 East.

That is why we have table tblGradeStream.

By default, if it is only one class holding students like 30 students, then it can be captured as:-


Grade 1 Single. Single in this case is Single Streamed or one class.

And it is working very fine.

 

[Mike Krailo]

I have to leave for the weekend so won't have any time to look into this right now, but I see your problem. You're trying to run the append query and it is not inserting any data into your tblPromote. Check to make sure all the data in the query results are of the correct data type. You have the table defined as short text for all the fields. Make sure you the query is producing short text and not number or date fields, otherwise it won't work.

I just changed your Years field in the query to a number to match what is in tblYears and the append query worked just fine.

 

[Gasman]

Run the query as a select first, to ensure you have the correct data, then change to an append query?

 

[GaP42]

OK -- you have a foreign key - the TermStreamsID in the tblStudents - with no data. It is not involved in any relationships and should be removed.

Your current qryPromote (append query) appends these records to the empty tblPromote. Is this what you expect? Repeating the process appends the same content - there is no unique index for the combination of items in tblPromote - you don't want duplicates to be generated?
Having this set of results - as below - would "Promote" be successful if Year was updated to 2024? If so just run a simple update query against the Promote table changing the Year value from 2023 to 2024.

Note: These are simply an isolated set of data that has no relationship to other tables in the database - it is essentially a report. It has no effect on the actual data about students and their placement in Grades or Streams.

qryPromote qryPromote

Student Name	Years	TERM	GradeDesc	STREAM
aaaaaaaaaaaaa aaaaaaaaaa	2023​	Term 1	PP1	SINGLE
bbbbbbbbbb bbbbbbbbbb	2023​	Term 1	PP2	SINGLE
cccccccccc ccccccccc	2023​	Term 1	GRADE 1	SINGLE
dddddddd ddddddddd	2023​	Term 1	GRADE 2	SINGLE
fffff fffff	2023​	Term 1	GRADE 2	SINGLE
ggggggg gggggg	2023​	Term 1	GRADE 6	SINGLE
vvvvvvvvv vvvvvvvvvv	2023​	Term 1	GRADE 6	SINGLE

You may have the idea you can then use this as a stage in the process of completing the enrolment of students to classes / Grades/Streams in the 2024 yr. You will need to ensure that there are no edits / additions/ deletions to any of the items and identities involved in the above.

Hope it helps.

 

[Mercy Mercy]

OK -- you have a foreign key - the TermStreamsID in the tblStudents - with no data. It is not involved in any relationships and should be removed.

Your current qryPromote (append query) appends these records to the empty tblPromote. Is this what you expect? Repeating the process appends the same content - there is no unique index for the combination of items in tblPromote - you don't want duplicates to be generated?
Having this set of results - as below - would "Promote" be successful if Year was updated to 2024? If so just run a simple update query against the Promote table changing the Year value from 2023 to 2024.

Note: These are simply an isolated set of data that has no relationship to other tables in the database - it is essentially a report. It has no effect on the actual data about students and their placement in Grades or Streams.

qryPromote qryPromote

Student Name	Years	TERM	GradeDesc	STREAM
aaaaaaaaaaaaa aaaaaaaaaa	2023​	Term 1	PP1	SINGLE
bbbbbbbbbb bbbbbbbbbb	2023​	Term 1	PP2	SINGLE
cccccccccc ccccccccc	2023​	Term 1	GRADE 1	SINGLE
dddddddd ddddddddd	2023​	Term 1	GRADE 2	SINGLE
fffff fffff	2023​	Term 1	GRADE 2	SINGLE
ggggggg gggggg	2023​	Term 1	GRADE 6	SINGLE
vvvvvvvvv vvvvvvvvvv	2023​	Term 1	GRADE 6	SINGLE

You may have the idea you can then use this as a stage in the process of completing the enrolment of students to classes / Grades/Streams in the 2024 yr. You will need to ensure that there are no edits / additions/ deletions to any of the items and identities involved in the above.

Hope it helps.

Still waiting for the query

 

[Mercy Mercy]

Hello experts. If there is any other alternative, please feel free to guide me.

 

[GaP42]

Based on what you have provided this is the update query to promote students to 2024:

UPDATE tblPromote SET tblPromote.Years = "2024";

Waiting for advice.

 

[Mercy Mercy]

Based on what you have provided this is the update query to promote students to 2024:

UPDATE tblPromote SET tblPromote.Years = "2024";

Waiting for advice.

GradeDesc also to promote a child from a class e.g Grade 1 to Grade 2.

So in this case we are going to have 2 queries.

An Append Query to input data to tblPromote and Update Query.

Is there any way to amalgamate the two queries into one?

Thanks in advance.

 

[GaP42]

GradeDesc also to promote a child from a class e.g Grade 1 to Grade 2.

So in this case we are going to have 2 queries.

An Append Query to input data to tblPromote and Update Query.

Is there any way to amalgamate the two queries into one?

Thanks in advance.

No. 2 different types of queries - however they can automatically be run in sequence without any delay from a button on a form with vba - one click. You need a form to enter/select the Year you want to promote to.

Also to automate, there is no defined sequence in grades (as I said to you before a column to tell access what grade follows a grade is needed). Further the source query will also list those who leave (ending school) - where will they be promoted to? You need to adjust the source query to not include these.

It is a simple matter to adjust the update query to update Grade 1 records to Grade 2, however when you do this in your real database, you will have students from all grades appearing in your list of appended records. Then the update process needs to know how to change each grade to the next - and this needs to be performed in a sequence that avoids updating those that have already had an update! (eg upgrade Grade 1 to Grade 2 then upgrading Grade 2 records in the list - which will include those already upgraded - to Grade 3 etc.)

 

[GaP42]

Your Promote Append Query, which does not include Grade 6 students - as they leave the school in SQL is

INSERT INTO tblPromote ( [Student Name], Years, TERM, GradeDesc, STREAM )
SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])) AS [Student Name], tblYears.Years, tblTerms.TERM, tblGrades.GradeDesc, tblStream.STREAM
FROM ((tblTerms INNER JOIN (tblYears INNER JOIN tblYearTerms ON tblYears.YearID = tblYearTerms.YearID) ON tblTerms.TermID = tblYearTerms.TermID) INNER JOIN ((tblGrades INNER JOIN (tblStream INNER JOIN tblGradeStream ON tblStream.StreamID = tblGradeStream.StreamID) ON tblGrades.GradeID = tblGradeStream.GradeID) INNER JOIN tblTermStreams ON tblGradeStream.tblGradeStreamID = tblTermStreams.GradeStreamID) ON tblYearTerms.YearTermID = tblTermStreams.YearTermID) INNER JOIN (tblStudents INNER JOIN tblStreamStudents ON tblStudents.StudentID = tblStreamStudents.StudentID) ON tblTermStreams.TermStreamsID = tblStreamStudents.TermStreamsID
WHERE (((tblGrades.GradeDesc)<>"Grade 6"));

The WHERE clause at the end is the only change.

For the promotion to grades there is a sequence of 7 append queries: promoting students from Grade 5 to Grade 6 ... to the Grade PP1 to Grade PP2.

Grade 5 to 6

UPDATE tblPromote SET tblPromote.Years = "2024", tblPromote.GradeDesc = "GRADE 6"
WHERE (((tblPromote.GradeDesc)="GRADE 5"));

etc till PP1 to PP2

UPDATE tblPromote SET tblPromote.Years = "2024", tblPromote.GradeDesc = "PP2"
WHERE (((tblPromote.GradeDesc)="PP1"));

Good luck

 

[Lightwave]

So a bit leftfield but just thought I'd say.

I've been burnt with update queries and normalisation in database design before so here's something to consider...

I find queries and maximisation of sub tables better for dynamic immutability. These are difficult concepts to initially get your head around until you have built a system the wrong way and then it becomes all too apparent..

Below is a link to a query pattern which is good when you need to continually promote values based on ORDER while maintaining immutability (updated queries can destroy immutability) - ie ordinal promotion.

So this works for change of ownership of valuable assets for instance. Or it could work well for latest position of students ie start date / end date anyone within those dates is still here.

MS Access – SQL to Select Distinct list of Child Records based on a Maximum or Minimum Child Field Value – Cloudy DATA Blog

cloudydatablog.net

 

[HermanMoreno]

The best way ... ? You need to start with a conceptual statement of what this database is for - what do you hope to be able to do with your database? Is it to manage the enrolment of students to Classes (Grades) year on year? Will you need to maintain student information, class (Grade) information, Teacher information? Fees for classes? Assessment Results? Attendance? Student Timetables? A statement of the scope / the problem(s) you want to solve. A full school administration system will require a sophisticated database - there are many that can be purchased. I assume this is not available to you and not what you desire.
By the Way - you can search for sample data models for school management systems - they will not fit your terminology but they might inform you about the data structures and relationships needed.

Start with the basics:
STUDENTS and the things that describe a student and nothing else eg Student Name, DoB(?), AdmissionNo, Date of Admission ...
GRADE (CLASS) and the things that describe a Grade (Class) and only the Grade - eg Class Name, enrolledStudents, Teacher assigned, Subject/Stream, Term, CalendarYear --- Implying that the concept of a grade is a specific collection of students being taught a unit /course/subject/stream by a teacher within a specific Calendar Year.
TEACHER: teacher name, dob, ...?

A Student may be enrolled in multiple Grades (Classes) and a Grade (Class) has many students enrolled. This is a real object. The StudentStream concept is I think something that emerges from the relationship between Students, Grades and Streams (Unit/Course/Subject)

A Teacher may conduct multiple Grades (Classes), and a Grade (Class) is conducted by a Teacher. (Although this may not be correct if you consider Teachers going on leave mid-term - if this need to be accounted then a table to resolve the many-to-many relationship is needed - capturing the date of departure/date of commencement for the teachers involved)

The above is represented by:
STUDENT m:n GRADE (Class) m:1 Teacher
which, applying normailsation rules
STUDENT 1-n ENROLEDIN n-1 GRADE n-1 TEACHER
Note:
EnrolledIn will have StudentID and GradeID as foreign keys
GRADE has the FK for a teacher

Streams? What are they? Are they UNITS of Study in a Subject?
Assuming they are, a STREAM (Unit) is taught in many GRADES (Classes), and a GRADE is held to teach a (1) STREAM (Unit)
STREAM 1:m GRADES
Grade then has a FK for STREAM.
If multiple teachers may be assigned the same Grade (class) - as in replacement due to leave - then an extra table is needed
ie Teacher !:n Grade becomes TEACHER m:n GRADE which is implemented as
Teacher 1:n Taught n: 1 GRADE with TAUGHT having a FK to Teacher and Grade and holding the data about commenced/ceased dates

As you were interested in "promoting" students, you will need to define the expected sequence for progression of students from one Stream (Unit) to the next Stream (Unit): eg BasicMaths 1 as step1, to BasicMaths 2 as step 2, to Geometry 1 as step 3, to Algebra1 as step 4 etc. If this is not how students progress then for eg Students may elect streams (units), then you will need to consider a process for future enrolment of students in grades rather than an automatic one or use the automated process and adjust the enrolments to match the elections.

I will leave it here to see if you can confirm/ take on board the above. There is more to do.

It seems like you are seeking assistance in designing a school administration database to manage student enrollment, class information, teacher details, fees, assessment results, attendance, and timetables. Here are some steps and considerations to help you with the database design:

1. Define the Purpose and Scope: Clearly state the purpose of the database and what you hope to achieve with it. Identify the specific problems you want to solve and the data you need to manage.
2. Identify Entities: Start with the basic entities involved, such as STUDENTS, GRADES (CLASSES), and TEACHERS. Determine the attributes (fields) for each entity, such as Student Name, Date of Birth, Admission Number for students, Class Name, Enrolled Students, and Teacher Assigned for grades, and Teacher Name, Date of Birth for teachers.
3. Establish Relationships: Determine the relationships between entities. For example, a STUDENT may be enrolled in multiple GRADES, and a GRADE is conducted by a TEACHER. Use appropriate foreign keys to establish these relationships.
4. Consider Many-to-Many Relationships: If a student can be enrolled in multiple grades and a grade can have multiple students, create a junction table (e.g., ENROLLEDIN) to resolve the many-to-many relationship.
5. Handle Streams/Units of Study: If streams or units of study are part of your system, create a STREAM entity and establish the relationships with GRADE (CLASS). You may need an extra table to handle multiple teachers assigned to a grade if required.
6. Plan for Student Progression: Define the expected sequence for student progression from one stream (unit) to another. If students have choices, consider a process for future enrollment or automate the process and adjust enrollments accordingly.
7. Normalization: Ensure that the database design follows normalization rules to eliminate redundancy and maintain data integrity.
8. Research Existing Models: Look for sample data models for school management systems, even if the terminology does not align with yours. They can provide insights into data structures and relationships needed.
9. Capture Extra Details: Consider capturing additional details like fees, assessment results, attendance, and timetables, and create separate entities or tables for these if needed.
10. Create Primary Keys: Assign primary keys to each entity to ensure uniqueness and enable easy retrieval of data.
11. Plan for Security and Access Control: Consider implementing appropriate security measures and access controls to protect sensitive data.
12. Iterative Process: Database design is often an iterative process. Review and refine the design as you gather more requirements and feedback from stakeholders.

Remember that building a comprehensive school administration system can be complex, so take your time, and consider involving other stakeholders to ensure the database meets the needs of all users.

 

[Mercy Mercy]

Research Existing Models: Look for sample data models for school management systems, even if the terminology does not align with yours. They can provide insights into data structures and relationships needed

Kindly assist me with a sample

 

[Mercy Mercy]

It seems like you are seeking assistance in designing a school administration database to manage student enrollment, class information, teacher details, fees, assessment results, attendance, and timetables. Here are some steps and considerations to help you with the database design:

1. Define the Purpose and Scope: Clearly state the purpose of the database and what you hope to achieve with it. Identify the specific problems you want to solve and the data you need to manage.
2. Identify Entities: Start with the basic entities involved, such as STUDENTS, GRADES (CLASSES), and TEACHERS. Determine the attributes (fields) for each entity, such as Student Name, Date of Birth, Admission Number for students, Class Name, Enrolled Students, and Teacher Assigned for grades, and Teacher Name, Date of Birth for teachers.
3. Establish Relationships: Determine the relationships between entities. For example, a STUDENT may be enrolled in multiple GRADES, and a GRADE is conducted by a TEACHER. Use appropriate foreign keys to establish these relationships.
4. Consider Many-to-Many Relationships: If a student can be enrolled in multiple grades and a grade can have multiple students, create a junction table (e.g., ENROLLEDIN) to resolve the many-to-many relationship.
5. Handle Streams/Units of Study: If streams or units of study are part of your system, create a STREAM entity and establish the relationships with GRADE (CLASS). You may need an extra table to handle multiple teachers assigned to a grade if required.
6. Plan for Student Progression: Define the expected sequence for student progression from one stream (unit) to another. If students have choices, consider a process for future enrollment or automate the process and adjust enrollments accordingly.
7. Normalization: Ensure that the database design follows normalization rules to eliminate redundancy and maintain data integrity.
8. Research Existing Models: Look for sample data models for school management systems, even if the terminology does not align with yours. They can provide insights into data structures and relationships needed.
9. Capture Extra Details: Consider capturing additional details like fees, assessment results, attendance, and timetables, and create separate entities or tables for these if needed.
10. Create Primary Keys: Assign primary keys to each entity to ensure uniqueness and enable easy retrieval of data.
11. Plan for Security and Access Control: Consider implementing appropriate security measures and access controls to protect sensitive data.
12. Iterative Process: Database design is often an iterative process. Review and refine the design as you gather more requirements and feedback from stakeholders.

Remember that building a comprehensive school administration system can be complex, so take your time, and consider involving other stakeholders to ensure the database meets the needs of all users.

Thanks so much for your comprehensive input. Kindly assist me with a sample database. Thanks in advance

 

[jdraw]

From #21

"Thanks for your help. My database is working fine. There are tables for school fees, Examinations and timetables I didn't deem it necessary to include in the query to promote students. It is working very well. If only I could be assisted in an Append Query to promote students, then I will be very grateful. Thanks in advance."

??How can these bolded sentences be true??