Query to promote students (1 Viewer)

[Mercy Mercy]

Hi all. I am requesting for assistance on a query to promote students on January every year. Kindly look at the attached database. Thanks in advance.

 

[GaP42]

You need to provide further advice: The only student records (IDs) are in tblStreamStudents. When the students are promoted (presumably to the next stream in the next year), how does this table relate to years? Is it to be accomplished through termstreams and yearterms? There are no relationships between the tables in the db.
Generally you would retain the original student history of enrolment in a stream, so you need to create an append query. How will you determine what streams the students are promoted to? Are they all assigned to the same stream at the start of a year? Where would this information come from?
Your table structure suggests: tblyears --> tblTermyears shows the terms in a year : these need to be set up for the "next" Yr. For each term there are multiple streams: tblStreamterms. Again before promoting a cohort of students, requires that the streamterms need to be established before any promotion occurs.
So are you expecting your query to not just promote the students but also replicate the current term-years and stream-term structure for the next year? What if there are changes in streams from one year to the next?

 

[The_Doc_Man]

You misunderstand the purpose and style of this forum. You are the subject matter expert for YOUR database and for YOUR problem. We can tell you how to approach things in general, but that approach ALWAYS starts with problem analysis. We can't analyze your problem with the small amount of information you told us and the cursory information in the sample DB. I would love to be able to read your mind and just whip up your solution for you, but my mind-reading turban is at the dry cleaners.

The best advice I can give you at this stage of the process based on what you have requested is that you try to draw this process on paper to see how you would do it if you DIDN'T have a computer. Draw out your diagrams and take notes on each step. Then come back to that diagram and try to implement it in code or queries.

 

[Mercy Mercy]

You need to provide further advice: The only student records (IDs) are in tblStreamStudents. When the students are promoted (presumably to the next stream in the next year), how does this table relate to years? Is it to be accomplished through termstreams and yearterms? There are no relationships between the tables in the db.
Generally you would retain the original student history of enrolment in a stream, so you need to create an append query. How will you determine what streams the students are promoted to? Are they all assigned to the same stream at the start of a year? Where would this information come from?
Your table structure suggests: tblyears --> tblTermyears shows the terms in a year : these need to be set up for the "next" Yr. For each term there are multiple streams: tblStreamterms. Again before promoting a cohort of students, requires that the streamterms need to be established before any promotion occurs.
So are you expecting your query to not just promote the students but also replicate the current term-years and stream-term structure for the next year? What if there are changes in streams from one year to the next?

It is one Grade to Many Streams e.g
GEADE 1 NORTH
GRADE 1 SOUTH
GRADE 1 GREEN

GRADE 1 NORTH
GRADE 2 SOUTH
GRADE 2 GREEN

AND SO ON.

Also a year has 3 TERMS
E.G
2023 TERM 1
2023 TERM 2
2023 TERM 3

There is no way a a term is related to Streams..
Also I have a feeling that it is Update Query which is to be used rather than Append Query.

 

[Mercy Mercy]

You misunderstand the purpose and style of this forum. You are the subject matter expert for YOUR database and for YOUR problem. We can tell you how to approach things in general, but that approach ALWAYS starts with problem analysis. We can't analyze your problem with the small amount of information you told us and the cursory information in the sample DB. I would love to be able to ready your mind and just whip up your solution for you, but my mind-reading turban is at the dry cleaners.

The best advice I can give you at this stage of the process based on what you have requested is that you try to draw this process on paper to see how you would do it if you DIDN'T have a computer. Draw out your diagrams and take notes on each step. Then come back to that diagram and try to implement it in code or queries.

Thanks for your input

 

[jdraw]

Step back. Think of what you would have to tell a contracted designer/developer in order to have him/her build your "proposed database". Perhaps some samples showing a student(s) in various Grades and various streams with examples of the promotion of specific students. As others have advised, we need more info.
Doc is our resident mind reader, and if he doesn't conjure up what you are thinking/describing to offer some semblance of a solution, then you'll have to provide more info. Rough diagrams, business rules, current processes, analogies..... whatever. Remember, we do not know you nor your environment, so communication is key to getting focused assistance.

 

[GaP42]

It is one Grade to Many Streams e.g
GEADE 1 NORTH
GRADE 1 SOUTH
GRADE 1 GREEN

GRADE 1 NORTH
GRADE 2 SOUTH
GRADE 2 GREEN

AND SO ON.

Also a year has 3 TERMS
E.G
2023 TERM 1
2023 TERM 2
2023 TERM 3

There is no way a a term is related to Streams..
Also I have a feeling that it is Update Query which is to be used rather than Append Query.

I still have no idea about what you are really trying to do with the data. Most of the questions remain unanswered. You also said "There is no way a a term is related to Streams.." however you have a table that appears to do this! Is it poorly named? Are the columns YearTerm and GradeStream referencing the Term and Stream. I have serious doubts about the db structure. Doc Man has provided valuable advice about how to get a solid handle upon the problem you need to solve.
Re an Update Query: as it will update existing records you will lose the history of what the students were enrolled in. You have no need to know about what stream they were enrolled in the past?

 

[Pat Hartman]

Also I have a feeling that it is Update Query which is to be used rather than Append Query.

This will NEVER be an update query. All the information associated with the previous term/stream/whatever stays and you start a new record for the next term/stream/whatever.

 

[mike60smart]

Hi
It looks like you have only uploaded part of your database.

There is no Student table ?

Hi all. I am requesting for assistance on a query to promote students on January every year. Kindly look at the attached database. Thanks in advance.

 

[Mercy Mercy]

Hi
It looks like you have only uploaded part of your database.

There is no Student table ?

There are so many tables tblStudents included. I might have overlooked but I thought those are the only tables needed for promotion of students

 

[Mercy Mercy]

This will NEVER be an update query. All the information associated with the previous term/stream/whatever stays and you start a new record for the next term/stream/whatever.

Thanks so much for your input

 

[Mercy Mercy]

Hello everyone. Kindly I am really requesting for assistance on how to promote the students every January every year. Kindly look at the attached database. Please I am defeated. I have tried Append Query. Please kindly assist me.

 

[GaP42]

I (we) do not understand what you want to happen. The query is a mess - it is impossible to understand, every table and relationship appears in your query, and you are trying to update the Student table which does not contain the items you are seeking to append!.
What does promote a student mean?
To take the lead from your query my GUESS is you want each student to:
1. progress to the next year - however the year of a student is only via Students - StreamStudents --> TermStreams --> TermsYears - Years
Assuming the future/new year has been created in the Years table (Yes), and the TermYear combinations for that new year exist (No), and the TermStreams combinations exist (No) then you may create (append) the studentStream records based upon the streams that they were enrolled in that continue in the next stream (I think?)
2. Or are you trying to move them from their current GRADE to the next Grade? Similar issues: Student - StreamStudents --> TermStreams --> GradeStream --> Grade. While grades have a gradeID in sequence as a PK there is no specific attribute dedicated to to say what is the sequence of Grades. You have labels for the grades, which people can read and see / imply the sequence, but that requires human interpretation and is not (readily) amenable to machine interpretation.

Use an example from your database and break it up to a process:

When Jan 2024 occurs you want a process in which:

TermYear records are appended representing the School Terms in 2024 (this in fact does not need to wait till Jan - can be done much earlier) - this is Append QUERY1: Append YearId (for 2024) and TermID (presumably static as there are no other attributes!)

TermStream records can then be appended representing the Streams available within a Term that apply to a Grade (a cohort of students) : Grades are static items in your db, every Grade has only 1 stream labeled "single" - as combinations in GradeStream, so another APPEND QUERY2 is needed to add records to TermStream: append for each TermYear (created in the previous append (identified by yearID), create a record with TermYearID, and GradeStreamID . Again this activity can be done prior to Jan as it does not affect students being assigned to streams

Next you need to assign the students to the streams ie append records in StreamStudents: APPEND QUERY3 : add studentID and the TermStreamIDs (only those appended in the previous query - I am not sure how you will identify these? You will need to rely upon a select query from Year to YearTerm to TermStreams where the Year = 2024. (This Append Query, when you are using the query for real, would be done in Jan - as that will on act upon the current student enrolment. Essentially students are promoted to streams in the "next" Grade.

That will complete the "promotion" that I think you need - at least a 3 step process. This all my interpreation - so a lot of caution here. You must verify - How would you describe the "promotion" process? Is this all that your application needs to do? Context is important.

If you cannot describe it we cannot make it up for you. Also we are not here to develop you application for you. This is very raw and seems to lack appropriate consideration of the data needed to operate - eg the Grade Sequence. You have to take on the responsibility to learn MS Access - and the process of db application development.

good luck

 

[Mercy Mercy]

You have to take on the responsibility to learn MS Access - and the process of db application development

Noted. I am still new in Ms Access. Thanks for your input.

 

[Mike Krailo]

I'm not understanding what a stream is. The only data in tblStream is the word "SINGLE". What are the other possible things that describe a stream? Need more sample data. But according to what your have, each student can have multiple streams (but we only know about the one called SINGLE) and each stream can have more than one student. This is the part that is probably confusing most of us.

In tblTerms you have Term 1, Term 2, Term 3. And in your relationship, it shows one Term can have many years and each year can have many terms. This part doesn't make any sense to me unless I'm completely misunderstanding what a term is. Also, can there ever be more than three different terms in a year? If not, you can simplify the table structure.

A term to me is a 1/3 of the period of time in any given school year. If that is the case you can just make a table that has the year and term combined as the primary key (or at least index them that way) and then any attributes that associate with that particular term. Maybe I'm missing something, but that makes more sense to me.

Usually students get grades in particular classes or subjects. In your tables, students have streams within a particular term and the grade seems to be assigned to a stream??? Very weird. I would think for a given subject/class a student would have a particular grade at the end of each term. I don't know, you tell us how this all works.

One thing is for sure, the tables have to be correct if you want to get any meaningful queries to work. It's better to simplify instead of making it too complicated at first.

 

[Mercy Mercy]

I'm not understanding what a stream is. The only data in tblStream is the word "SINGLE". What are the other possible things that describe a stream? Need more sample data. But according to what your have, each student can have multiple streams (but we only know about the one called SINGLE) and each stream can have more than one student. This is the part that is probably confusing most of us.

In tblTerms you have Term 1, Term 2, Term 3. And in your relationship, it shows one Term can have many years and each year can have many terms. This part doesn't make any sense to me unless I'm completely misunderstanding what a term is. Also, can there ever be more than three different terms in a year? If not, you can simplify the table structure.

A term to me is a 1/3 of the period of time in any given school year. If that is the case you can just make a table that has the year and term combined as the primary key (or at least index them that way) and then any attributes that associate with that particular term. Maybe I'm missing something, but that makes more sense to me.

Usually students get grades in particular classes or subjects. In your tables, students have streams within a particular term and the grade seems to be assigned to a stream??? Very weird. I would think for a given subject/class a student would have a particular grade at the end of each term. I don't know, you tell us how this all works.

One thing is for sure, the tables have to be correct if you want to get any meaningful queries to work. It's better to simplify instead of making it too complicated at first.

Thanks for your input. Relationships should be as follows

One to many.
1. One year to have 3 terms.

2. One Grade to have many Streams.

Grade in this case Grade is Class.

How do I represent those relationships in referential integrity?

Thanks in advance.

 

[Mercy Mercy]

Normalization tables

 

[Mercy Mercy]

Relationships. Please assist where necessary. Thanks in advance.

 

[Mercy Mercy]

In essence, I am trying to avoid redundancy. What is the best way?

 

[GaP42]

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.