Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Tracking students (XP & 2000)

    I don't know why I'm having such a hard time with this, but...maybe I've been staring at it for too long. I have a DB to track 30 new employees through a training program. Every student has to complete the same program. We have 24 (I think) subjects to cover; some subjects include more than one "class" (e.g., two videotapes that cover the same subject), so we have, say, 30 classes to track. About half the training is in the classroom, where all the students get the same stuff together. The rest they do individually in our learning labs.

    So I have a table for students. Then I have a table for Subjects, another for classes, and a linking table to pair the classes with the correct subjects. Now what I need is a form that we can use to document when each student completes each piece of the training. I've seen another off-the-shelf application where the student information appears on one page or tab. You double click on a name, and the app takes you to another form that displays the student's identification and has a subform to display the courses that student has completed and the date (s)he completed each course. That's the part I can't figure out yet. Can someone offer some suggestions, please.

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

    Re: Tracking students (XP & 2000)

    I would start by creating a query that returns student IDs, completed courses and completion dates. Something like this:

    <table border=1><td>StudentID</td><td>Course</td><td>Date</td><td align=center>37</td><td>Eating crow</td><td>10/21/03</td><td align=center>37</td><td>Pushing up daisies</td><td>10/28/03</td><td align=center>62</td><td>Singing the blues</td><td>09/14/03</td></table>
    Can you do that?

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Tracking students (XP & 2000)

    That's kinda what I'm shooting for, Hans, and I can create such a query. My thought process goes as follows:

    For the courseware side of said query, I think I'd use the linking table that connects the Subjects and Classes tables. (Most subjects have just one class; others have two or three). So in the query design window, I'd have two tables: tlnkSubjectsClasses and tblStudentNewHires. Here's where I get bogged down: What's the relationship between the two tables for query purposes? Maybe I need to create yet one more table, i.e., a linking table between tlnkSubjectClasses and tblStudents...? If I build that table, I shouldn't need a query, eh? I could build a form with a subform:

    Main form: Student information (payroll ID, name and all that)
    Subform: Classes completed. (Now the next question: How best to populate that form and its underlying table...?)

    Writing all this out is giving me some ideas. I'll be eager to see your response, and meanwhile I'm go tinker with this a bit.

    Thanks for your help!

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Tracking students (XP & 2000)

    OK, I'm trying to build the linking table I kinda/sorta described in my last post, and I've already hit a snag.

    I call this table tlnkStudentsSubjectsComplete. First I laid in an AutoNumber field: StudentSubjectsNbr. Next I added the primary key field from the tblStudents table--it's supposed to pull in the primary key field data via the LookupWizard. I knew I had a problem when I saw the error "#name?" while going through the LookupWizard. Why am I getting this error?

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

    Re: Tracking students (XP & 2000)

    I don't know if you should create a table that contains only completed courses. Wouldn't a table with all courses taken by a student be better? If the "Date_Completed" field is filled in, the student has completed the course. Of course, I don't know the exact situation.

    I don't know why the lookup field failts. You''ll have to provide information about the structure of the existing tables. By the way, I don't think that this linking table needs an AutoNumber field; you need a composite primary key on student and course (unless a student can do the same course twice).

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Tracking students (XP & 2000)

    No, I'm not trying to create a table with only completed courses. I have tables for courses and students

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

    Re: Tracking students (XP & 2000)

    Your idea of a linking table is correct. The reason to have a composite primary key, or at least a unique key on the combination of StudentID and CourseID is that this prevents the user from accidentally entering the same course for a student twice. See screenshot for possible table structure.
    Attached Images Attached Images
    • File Type: png y.PNG (6.2 KB, 0 views)

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Tracking students (XP & 2000)

    Thanks, Hans! Now I gotta figger out why my attempt to build such a linking table failed so miserably yesterday (re: my earlier post in this thread).

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Tracking students (XP & 2000)

    Hans et al: Here's a dummy version of my database. Would you be so kind as to try to find out why I can't create a table that links tblCourses and tblStudents. I keep getting the "#Name?" error in the Lookup Wizard. Thank you.
    Attached Files Attached Files

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

    Re: Tracking students (XP & 2000)

    Strictly speaking, it is not necessary to have lookup combo boxes in the table. But I had no problems creating a tblStudentCourses table with fields PID and ContrlNbr (and DateCompleted). See attached database.
    Attached Files Attached Files

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Tracking students (XP & 2000)

    Now I'm really confused, Hans. You say it's not necessary to use lookup combo boxes. But how else does one build these things?

    I'm glad you didn't have any trouble creating a tblStudentsCourses, but I'll be sheep-dipped if I can figure out why I couldn't do it. I even tried it one last time before I posted the attachment...after I created the dummy DB for the attachment. So you and I used exactly the same "raw materials." Did you use the Lookup Wizard?

    Again, thanks for your help!

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

    Re: Tracking students (XP & 2000)

    Hi Lucas,

    Yes, I did use the Lookup Wizard to create the PID and ContrlNbr fields in tblStudentCourses. Worked without a hitch.

    It isn't necessary to use combo boxes in the table since the end user will (or should) never add or edit a record in tblStudentCourses in the table itself. The user will (or should) always use a form; this will probably be a form based on the students table, with a subform for the courses based on tblStudentCourses. The subform would be linked to the main form by PID, so no combo box is needed for PID on the subform (in fact, PID will not be visible on the subform); there will be a combo box for ContrlNbr but that can be set up within the subform itself.

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Tracking students (XP & 2000)

    Good morning, Hans

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Tracking students (XP & 2000)

    I understand

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

    Re: Tracking students (XP & 2000)

    You can display PID in the main form, but I wrote that you wouldn't want to display it in the subform - that seems useless to me, since all records in the subform will have the same PID.

    The combo box for ContrlNbr in the subform should actually show the name or description of the courses, not the AutoNumber field itself; this is accomplished by having more than one column, and hiding the first one (bound to ContrlNbr) by setting its width to 0.

Page 1 of 2 12 LastLast

Posting Permissions

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