Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Making a course timetable (Access 2002)

    I have a table with courses. I have a table with students. I have a table with dates. How do I marry these up so I can look at a form and create a report that shows the courses and the students that are taking them on different dates AND another form and report with the students showing all of the courses they have taken? I want to be able to add the student to the course from either form.

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

    Re: Making a course timetable (Access 2002)

    You have many-to-many relationships, for example one student can take several courses, and several students can take the same course.

    Such a relationship is stored in the database by creating a join-table in which each record represents a student-course combination. If student A takes 5 courses, there will be 5 records for student A in the join table. And if course 101 is taken by 20 students, there will be 20 records for course 101.

    Data entry is through a main form based on one of the "main" tables and a subform based on the join table. You can have a main form based on the student table with a subform in which you can select courses taken by the student. And you can have a main form based on the courses table with a subform in which you can select students that take the course.

    How the dates come into this depends on what exactly you want.

    See the sample database attached to <post:=364,203>post 364,203</post:> - it demonstrates the approach I described.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a course timetable (Access 2002)

    Looked at the file you did. Tried it with a sample database to see if I could do something similar and failed miserably. What am I doing wrong? I'd love to send you what I have created, but WinZip can't get it down to 100k. I've stripped the file as much as I can. It is really small - only 5 students and 6 courses. Any ideas?

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

    Re: Making a course timetable (Access 2002)

    It's impossible for me to say whay goes wrong without seeing the database. See <post#=401925>post 401925</post#> for instructions on reducing the size.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a course timetable (Access 2002)

    Hans, thanks for the help. Here is the database I have constructed so far.

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

    Re: Making a course timetable (Access 2002)

    The essential points:
    - The FrmCoursesSubForm should be based on TblEvent.
    - The first combo box on this form should be bound to StudentID (it's unbound now).

    Some other remarks:
    - Remove duplicate indexes.
    - Set the SubDatasheet Name property of all tables to [None]

    I have attached a modified database (in Access 2000 format, zipped).

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a course timetable (Access 2002)

    Unfortunately, my computer would not let me open the extracted zipped file. I even copied it, as per instructions.

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

    Re: Making a course timetable (Access 2002)

    Did you get an error message that it has been blocked or something similar? If so, right-click the .mdb file in Windows Explorer, select Properties from the popup menu, activate the General tab and click Unblock. If that was not the problem, what prevented you from opening the extracted file?

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a course timetable (Access 2002)

    Hans, you are wonderful. If you ever get to Canada (you live in the Netherlands, right?), you are welcome to stay at our bed and breakfast for a couple of nights for free. I appreciate what you are doing for me and others. You can click on the hyperlink below my name to get a glimpse.

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

    Re: Making a course timetable (Access 2002)

    Thanks for the offer - it does look wonderful! I don't know if I'll visit Canada anytime soon, though...

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a course timetable (Access 2002)

    Thanks for what you did. I have been playing around for hours trying to make the reverse happen. Here is what I want. I want to be able to add a student to a course, but I also want to be able to add a course to the student and see all the courses each individual has taken. Is this possible? I have attached my feeble attempts to untie this knot.

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a course timetable (Access 2002)

    Found it!!!! Noticed that the Allow Additions on the student subform was set to NO. It works like a charm now. Thanks a million.

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

    Re: Making a course timetable (Access 2002)

    The record source for the subform was too complicated. Again, it should simply be the TblEvent table. I have attached the modified database.
    (I also changed the primary key in TblEvent to prevent a student from taking the same course twice)

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a course timetable (Access 2002)

    Thanks for everything, 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
  •