Adding courses to a group of students automatically using a button

[Wabamdo]

Hi guys,how can I add courses to a group of students in my School management system automatically using a button?
The students are on different levels at different times.For instance,students who are taking course F1 in 2015 will be taking F2 course at the start of 2016 while those who were taking F2 will be taking F3 at the beginning of year 2016.


How will I do it automatically without going to each individual student and adding the course manually?

[ranman256]

make an append query of the current students for their courses in tStudentCourses table
but instead of adding their current course field , use the NEW course 'name' (hardcoded)

insert into tStudentCourses ([studentid], [Course], [year]) select ([studentid], "NewCourse", '2016' ) from tStudentCourses where [course] = '" & oldCourse & "'"

[Wabamdo]

Thank for your help so far,will you be kind enough to provide me with a sample db or at least a link? I am just starting out in ms access

[orange]

wabamdo,
Why don't you post a copy of your School management system? It could be helpful to others.

[Wabamdo]

wabamdo,
Why don't you post a copy of your School management system? It could be helpful to others.

Below is a copy of the database I'm creating,please have a look at it and guide me in creating the append query

[orange]

What exactly does this mean?

The students are on different levels at different times.For instance,students who are taking course F1 in 2015 will be taking F2 course at the start of 2016 while those who were taking F2 will be taking F3 at the beginning of year 2016.

It isn't clear to me where level fits in your relationship view. Things like studentsCoursesMain CoursesMain are new terms to me. You have a lot going on in the diagram. I don't see any connection between Student and Teacher.

I don't think readers know enough about your business to make meaningful comments.

It seems that you are dealing more with students, school year and invoices.

Could Courses not change from year to year?
Could students not fail or drop out/quit the current course?

It just isn't clear to me how the pieces fit. Perhaps ranman256 understands and has provided you with part of a solution - to change the Year to 2016, and change say F1 to F2 for students currently in F1.

If you have designed and developed the database attached, then I'm sure you could find a sample database with an append query.


see this for more on Append query.

[Wabamdo]

Please allow me to make give you guys more explanation because I am still stuck.In the database that I am developing ,there are two categories of students :BOARDERS and DAY SCHOLARS.(The BOARDERS are the students who sleep at school) thus the tblCOURSETYPE. The boarders and Dayscholars pay different FEES AMOUNTS for the same course.The name of the courses are F1,F2,F3,F4(F1 means Form 1 and so on)-I think this is according to british education system of Secondary school).
Different COURSES attract different FEES AMOUNTS. tblCoursesMain is therefore the junction table between tblCourses and tblCourseType since student A can take for example Course F1 of CourseType BOARDER, in a particular year,then shift to COURSE F2 of CourseType DAY SCHOLAR in the following year and so forth.The COST that the student pays as fees depends on the COURSE that he is taking and the COURSEtype at that particular year.

tblStudentCoursesMain is the junction table between the tblStudents table and tblCourseMain table.It is like what RanMan256 refers to as the tStudentCourses table in his earlier explanation on how to create an append query

Please guys review my question again on how to create an append query that automatically adds a record in the tblStudentCourseMain using a button.I am open to suggestion on how I can modify my table design to accomplish the task ahead

I had attached the SAMPLE database already so feel free to have a look at it.

Thanks guys in advance.

[June7]

Careful about circular relationships - I see possibly one in your db, review http://www.codeproject.com/Articles/...atabase-Design
Not sure you need StudentType field. The student's current 'type' can be determined by the current course they are enrolled in, presuming a student can be in only one course at any time.

Teachers process invoices?

Back to ranman's suggested SQL. Code behind button:

CurrentDb.Execute "INSERT INTO tblStudentsCourseMain(StudentID, CourseMainID, DateAdded, CourseCost, Discount, YearAttended) SELECT ...

What I am not clear on is how to select the student records you want to use as basis for new set of records. So you need to show us this. Build a SELECT query that includes the tables and fields needed as a template and the filter criteria. This will become the guide for structuring the SELECT part of the INSERT SELECT sql action. A simple example of an INSERT SELECT in VBA:

CurrentDb.Execute "INSERT INTO tblPayroll(PayrollID, EmpID) SELECT " & Me.tbxPayrollID & " AS PID, EmpID FROM tblEmployees WHERE EmpStatus = 'Active'"

Will also need to take steps to prevent duplicate entries - like if the button is accidentally clicked again. Options:

1. set compound index in table to prevent duplicate combinations of StudentID and CourseMainID and DateAdded.

2. expand the VBA procedure to first check for existence of the data combination in table

[Wabamdo]

First and foremost,Thanks to you guys for I am making some progress.

June7,I am looking for a way to automatically add courses that the students will be taking at the beginning of every year,but I also wanted to make sure that if a student was taking course F1 in year 2015 ,course F2 will be automatically added to his course records in 2016 when I click an add courses button.

In the example that you provided :-CurrentDb.Execute "INSERT INTO tblPayroll(PayrollID, EmpID) SELECT " & Me.tbxPayrollID & " AS PID, EmpID FROM tblEmployees WHERE EmpStatus = 'Active'"
please provide some guidance on what Me.tbxPayrollID is and how it can help me solve the problem that I have provided details for

I am really grateful for your time

[June7]

It was a simple example of an INSERT SELECT sql action. Me.tbxPayrollID is reference to a textbox on a form where user has selected the pay period for which new records need to be created.

Whether or not this model can be applied in your situation is to be determined. Maybe would require more than one INSERT SELECT.

CurrentDb.Execute "INSERT INTO tblStudentCourseMain(StudentID, CourseID, DateAdded, YearAttended) SELECT StudentID, 'F2' AS CID, Date() AS DA, 2016 AS YA FROM tblStudentCourseMain WHERE CourseID = 'F1' AND YearAttended=2015"

So that action will take everyone currently enrolled in F1 for 2015 and create records that enroll them into F2 for 2016. Is that what you are looking for?

Maybe a more complex VBA procedure using recordsets and looping and conditional structures will be more efficient. As orange notes, there are a lot of pieces to this process that just aren't clear enough to us to provide specifics and always more than one way to accomplish.

I expect development of a full procedure will be quite involved. We can direct you toward some possible avenues of attack but you will have to make effort to flesh out. If you don't already have knowledge of programming concepts and VBA language and syntax, structuring SQL statements and manipulating recordsets, then this will be especially challenging.

[orange]

I think June's question shows how I interpreted you post also.

....So that action will take everyone currently enrolled in F1 for 2015 and create records that enroll them into F2 for 2016. Is that what you are looking for?....

This seems to be a technique to help with enrollment and billing set up for the next year for students currently in F1,F2 and F3. General approach below uses Fx where x is 1,2, 3.

Take all students currently in Fx an create records to "enroll them" in Fx+1 for current year +1.
Do not do this for students currently in F4 --they should have graduated/completed their activities at the school.
So only new students entering F1 in the next year have to be entered into the system.
Because your fees depend on student type - dayscholar, Boader - you "billing info" needs to respect student type in your calculations.