Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    5

    Lightbulb Unanswered: Needs help in developing a database

    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!
    Last edited by st0ic0ne; 08-13-04 at 20:07.

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    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.
    ~

    Bill

  3. #3
    Join Date
    Jul 2004
    Posts
    67
    st0ic0ne & savbill,

    To add to savbill's suggestion...You can accomplish the same basic procedure with one query using NOT EXISTS if you write it in SQL mode.

    -David

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    Your correct David.

    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
    Code:
    SELECT DISTINCT tblCourse.CourseID, tblCourse.CourseName
    FROM tblStudentCourse RIGHT JOIN tblCourse ON tblStudentCourse.CourseID = tblCourse.CourseID
    WHERE ((Not (tblStudentCourse.StudentID)="ma"));
    Last edited by savbill; 08-15-04 at 10:17.
    ~

    Bill

  5. #5
    Join Date
    Jul 2004
    Posts
    67

    Even better

    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.

  6. #6
    Join Date
    Aug 2004
    Posts
    5

    Thumbs up Thanks

    Thanks for helping me out guys. I owe you this one.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •