I'm new to using ms access, so I really need your help guys. Not quite familiar yet about it at all, so be patient with me.
I am developing a database, which is supposed to take and store printable information regarding students and their academic records from a form. I originally used text boxes to input data in all fields. But later on converted the ones pertaining to classes into combo boxes to speed up the encoding process. The 3 up to 10 combo boxes I have in each semester shows all the classes that students have to take in their entire 4-year course from a single row source ("Classes" table). Now the challenge is how to make it possible for me and other users to see a list of all the classes that a student haven’t taken yet on a separate form or list box, without altering the single row source of the combo boxes I have mentioned above.
Can either post here or email me your replies. Any suggestion or guidance will be highly appreciated. Thanks!
Without knowing more about the structure of your database it is difficult to give a specific answer to this. I assume you have a table of all available classes, then you may also have a linking table to define which curriculum go with a student based on thier major? and you must have a table listing the stundent ID to the courses they have completed.
Your objective will be to create a query to list all the courses a student has. Then create another query which includes the courses table and the studentCourses query you previously created with a Join between AllCourses and StudentCourses on the course ID.
You would limit the list by criteria of the student ID, and Courses where the courseID is Null.
You will need to add formulas to your query to refer to the specific StudentID on your form. Or run the query on the fly to have dynamic results based on the current studentID in your form.
I tested a Query based on having a table for Student Courses [tblStudentCourse] and a Courses Table [tblCourse] with a join on the CourseID between the tables. Keep in mind you'd use an Expression in the where clause to refer to the current StudentID field on the active form where the query/list is displayed.
btw. I was able to do this in the Query Design Mode using 'Select Distinct'.
Query Lists All Courses Not Completed by Student
SELECT DISTINCT tblCourse.CourseID, tblCourse.CourseName
FROM tblStudentCourse RIGHT JOIN tblCourse ON tblStudentCourse.CourseID = tblCourse.CourseID
WHERE ((Not (tblStudentCourse.StudentID)="ma"));
Yup, that's even better. NOT EXISTS is more useful when the 'student name' (in this case) is null, or rather when you're looking for classes that no student has signed up for. Your query seems right on the money.