Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find classes a student has and has NOT Taken (Access 97)

    Hey Gang,

    First of all, I just want to say that I appreciate the service and everyone who participates. I'm having a tough time with a bit of logic in Access. I have to create an Access Database that tracks classess students have taken for a competency. I would like to have a main form and several subforms on a tab control to make entering information easier. I would only need four subforms that relate to different training areas. In each of those training areas, there are certain classess once has to take. Because I don't want an instructor forgetting a class in that area, I want all the classes to show, not just the classes a student has taken.

    So for instance, if the Training Area was "Database Competency" and there were 3 classes you needed in order to demonstrate competency, I would want to list Access Intro, Access Intermediate, and Access Advanced-regardless of whether or not you have actually taken any of the classes. In the subform, I would like to record the date that you have taken the class, who the instructor was and the student's ID. (Easy enough)

    However, I can only get Access to show me the classes that person has taken between the main form and the subform. I have tried all sorts of things:from Join Types; to not synchronizing by using master and child fields, but rather control criteria in the SQL Statement; to creating a calculated control to tag classess that I could later filter. Nothing...

    I'm trying to stay away from the combo box because that would just be cumbersome. This needs to be more like a checklist. (But I don't want to use checkboxes either)

    I feel like I'm close, but can't quite understand how you acheive this in Access. Any help would be great. Thanks again.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find classes a student has and has NOT Taken (Access 97)

    You need an intermediary table with fields StudentID, ClassID plus other needed information, such as the date etc. When a new StudentID is created in the students form, populate this table with records for that StudentID and each ClassID. That way, ALL classes for a training area will appear in the subforms.

  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find classes a student has and has NOT Taken (Access 97)

    Thanks for your speedy response. I too thought about that over the weekend, but I'm unsure how to begin. I have a feeling that I enter the student information and then click a button or have some event triggered that automatically fills in the classes. Could you point me in the right direction to accomplish such a task through VBA? Or have some web resources that I can see something similar?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find classes a student has and has NOT Taken (Access 97)

    There is a recent thread about the same problem, but in a different context. It starts at <post#=365875>post 365875</post#>. See if that helps you on your way. If not, feel free to post back.

  5. #5
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find classes a student has and has NOT Taken (Access 97)

    One word: Brilliant. Thanks Hans.

Posting Permissions

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