Setting up tables for grades
in [Form]
|
Prev: Tab sequencing not working
Next: Subforms limitation
From: Gntlhnds on 23 Apr 2010 12:51 This is what I am trying to do. I have a form whose source is tblStudents with a combo box that selects the student. I then have a subform whose source is tblCourseGrades as a datasheet. I have two field, Course and Grade. Course is a combo box with tblCourses as it's source to select the Course. This is not working for me, though. Becasue tblCourseGrade does not have a Course field, I have to have the combo box unbound, which does not work. If anyone has any idea as to how I should properly setup this subform, I would greatly appreciate it. Thanks. "John W. Vinson" wrote: > On Fri, 23 Apr 2010 07:48:01 -0700, Gntlhnds > <Gntlhnds(a)discussions.microsoft.com> wrote: > > >Thanks for the response. The best way to describe my situation would be like > >an elementary school, where each class takes the same courses at the same > >time. And, no, my tblCourseGrades is not populated with the courses and > >students. I'm assuming I would use a query to do this, but how would I work > >that? I have classes starting about every two weeks or so, and each class > >takes the same courses, so I would need to frequently update that table. I > >hope this is enough information. Thanks. > > A Query probably will be involved, but you would (I would guess!) need to pick > somebody's brain for the information that Alisia Gonzalez is assigned to the > Management Development class starting May 3. Normally one would do this using > a form and subform arrangement; this could be done in two ways - you could > have a form based on the table of students with a subform based on what I'd > call the "class enrollment" table, allowing you to pick whichever classes this > student needs. Alternatively (or in addition) you could have a form based on > the class table, with a subform allowing you to pick students from a combo box > to enter them into the enrollment table. > -- > > John W. Vinson [MVP] > . >
From: Gntlhnds on 23 Apr 2010 12:55 Really what I would like is to have the subform list all of the courses with the ability to add the grade once the student completes the course, then I can create reports to output the grades either by student or class, including a composite grade. "Gntlhnds" wrote: > This is what I am trying to do. I have a form whose source is tblStudents > with a combo box that selects the student. I then have a subform whose > source is tblCourseGrades as a datasheet. I have two field, Course and > Grade. Course is a combo box with tblCourses as it's source to select the > Course. This is not working for me, though. Becasue tblCourseGrade does not > have a Course field, I have to have the combo box unbound, which does not > work. If anyone has any idea as to how I should properly setup this subform, > I would greatly appreciate it. Thanks. > > "John W. Vinson" wrote: > > > On Fri, 23 Apr 2010 07:48:01 -0700, Gntlhnds > > <Gntlhnds(a)discussions.microsoft.com> wrote: > > > > >Thanks for the response. The best way to describe my situation would be like > > >an elementary school, where each class takes the same courses at the same > > >time. And, no, my tblCourseGrades is not populated with the courses and > > >students. I'm assuming I would use a query to do this, but how would I work > > >that? I have classes starting about every two weeks or so, and each class > > >takes the same courses, so I would need to frequently update that table. I > > >hope this is enough information. Thanks. > > > > A Query probably will be involved, but you would (I would guess!) need to pick > > somebody's brain for the information that Alisia Gonzalez is assigned to the > > Management Development class starting May 3. Normally one would do this using > > a form and subform arrangement; this could be done in two ways - you could > > have a form based on the table of students with a subform based on what I'd > > call the "class enrollment" table, allowing you to pick whichever classes this > > student needs. Alternatively (or in addition) you could have a form based on > > the class table, with a subform allowing you to pick students from a combo box > > to enter them into the enrollment table. > > -- > > > > John W. Vinson [MVP] > > . > >
From: John W. Vinson on 23 Apr 2010 13:03 On Fri, 23 Apr 2010 09:51:01 -0700, Gntlhnds <Gntlhnds(a)discussions.microsoft.com> wrote: >This is what I am trying to do. I have a form whose source is tblStudents >with a combo box that selects the student. I then have a subform whose >source is tblCourseGrades as a datasheet. I have two field, Course and >Grade. Course is a combo box with tblCourses as it's source to select the >Course. This is not working for me, though. Becasue tblCourseGrade does not >have a Course field, I have to have the combo box unbound, which does not >work. If anyone has any idea as to how I should properly setup this subform, >I would greatly appreciate it. Thanks. Well... if you want to record the course grade for a student, you must - no option, no choice - record SOMEWHERE what course the grade is for!!! Why doesn't tblCourseGrade have a Course field? What is its structure? The problem isn't your form. It's your table. -- John W. Vinson [MVP]
From: John W. Vinson on 23 Apr 2010 13:05 On Fri, 23 Apr 2010 09:55:01 -0700, Gntlhnds <Gntlhnds(a)discussions.microsoft.com> wrote: >Really what I would like is to have the subform list all of the courses with >the ability to add the grade once the student completes the course, then I >can create reports to output the grades either by student or class, including >a composite grade. Don't confuse data *DISPLAY* - a subform - with data *STORAGE* - a table. Data is NOT stored in forms. It's stored in tables, and only in tables. You can display multiple courses on the form if you like, but it sounds like you're assuming that the grade is being put into the form. It's not; you're using the form as a tool to put the grade (and the student ID and the course) into a Table. You can then use queries and reports to pull the data back out of that table in any form or combination you like. -- John W. Vinson [MVP]
From: Daryl S on 23 Apr 2010 13:49
Gntlhnds - Like John said, you could use a query or a form to assign students to their classes. If all students that come through your school take the same courses, and in the same order, or they all start on the same day, and all students take all 13 of your courses, then you can write a query to append the records when the student is added. I worry that your design is not very flexible, and that if something changes you will need to do a lot of rework. Some things to consider (you can choose what you want to implement depending on your business model): 1. You really offer two programs, some classes of students are in program A that take courses a100, a200, a300, a400, and a500, while some classes of strudents are in program B that take courses b100, b200, b300, b400, b500, b600, b700, and b800. If this case, you need to create a programs table and assign courses to the programs (maybe course a100 is given in both programs because it is an introduction class). Then when you set up your student, you assign them a program, and you can use a query to append the records for each student based on the program. 2. Some students will take only some of the classes. You don't want to show students in a class with no grades if they don't take the class. If your classes are, say, 2 weeks each, and the program is supposed to last 26 weeks, then some students may drop out after week 10. In this case, you don't want to assign all students to all courses up front. Maybe all 13 courses are taught in 1/2-hour blocks every day, so all students really do start all classes. Then you would assign them all up front... 3. I really think you will want some dates in your tables. If the courses are taken sequentially, then you may want to include the course start date in your tblCourseGrades (which I am thinking is more like a tblCourseEnrollment, one attribute of which would be the final grade). A completion date would be good unless that can always be determined from the start date (e.g. your programs always take 6 weeks to complete). Anyway, if you just want to add all courses for each new student with your current structure, then you could add a button on the form you use to add a student, and clicking the button could run a query like this (use your form and control name to get the current StudentID from the form): INSERT INTO tblCourseGrades(CourseID, StudentID) SELECT CourseID, Forms!StudentForm!txtStudentID FROM tblCourses; If you can provide more details, we can provide more help... -- Daryl S "John W. Vinson" wrote: > On Fri, 23 Apr 2010 07:48:01 -0700, Gntlhnds > <Gntlhnds(a)discussions.microsoft.com> wrote: > > >Thanks for the response. The best way to describe my situation would be like > >an elementary school, where each class takes the same courses at the same > >time. And, no, my tblCourseGrades is not populated with the courses and > >students. I'm assuming I would use a query to do this, but how would I work > >that? I have classes starting about every two weeks or so, and each class > >takes the same courses, so I would need to frequently update that table. I > >hope this is enough information. Thanks. > > A Query probably will be involved, but you would (I would guess!) need to pick > somebody's brain for the information that Alisia Gonzalez is assigned to the > Management Development class starting May 3. Normally one would do this using > a form and subform arrangement; this could be done in two ways - you could > have a form based on the table of students with a subform based on what I'd > call the "class enrollment" table, allowing you to pick whichever classes this > student needs. Alternatively (or in addition) you could have a form based on > the class table, with a subform allowing you to pick students from a combo box > to enter them into the enrollment table. > -- > > John W. Vinson [MVP] > . > |