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

    Select course number by semester (XP)

    Our database tracks "courses" (actually training pieces that take between 0.5 hr to 4 hrs to complete). Our students are employees at an industrial site. We have our own internal system for tracking completions, but then we also post that information to a state community college system. Our own system cares nothing about semesters, but the community college system does, of course. The college system assigns a unique six-digit number to each of our courses and another five-digit number to identify each course according to the semester a student completed it.

    So, I have a table of semesters with start and end dates. In our internal system, the user enters a course completion for a given student and enters the date the student completed the course. What I want is for the system to automatically select the correct course number according to the semester. That is, if I key in today's date, it will know to pick the number that goes with the Summer 2003 semester. I hope that's a clear explanation--can anyone start me on the right path to set up such an arrangement. Thanks!

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Select course number by semester (XP)

    Can you spell out what exactly is in your tables.

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

    Re: Select course number by semester (XP)

    Of course. (Sorry--I wrote my previous post in a hurry.) The main tables are tblStudents, tblCourses (and a linking table, tblStudentCourses), then tblSemesters. tblCourses includes the course title and the various numbers that identify them to various users: CourseID (six digits; number never changes); ClassNbr (five digits; changes with each semester). tblSemesters stores a four-digit number that the state system uses to uniquely identify each semester; other fields store the plain-English name of the semester (e.g., Spring 2003), beginning date and ending date.

    The most useful thing I can furnish will be a sample DB stripped of any real data. I can do that, of course, although I'm not in a position to do that at this moment. If the above doesn't explain things well enough, I'll try to do better tomorrow.

    Thanks very much for trying to make sense of my gibberish. I really appreciate the help!

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

    Re: Select course number by semester (XP)

    In the first post in this thread you stated that tblSemesters contains the start and end dates of each semester, so given a date, it is possible to determine the matching record in tblSemesters, including the four digit semester identifier.
    But it is not clear from your description how to use this to determine the five digit ClassNbr for a given course. What is the relation between semester identifier and ClassNbr, and between ClassNbr and CourseID?

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

    Re: Select course number by semester (XP)

    Good morning, Hans--I apologize for my foggy explanations. This thing gets convoluted, in part because I'm trying to design one DB that will accomodate two completely separate record-keeping systems. Our local system is fairly simple--I have 200-plus courses, numbered starting with 1 and going to 243. I have about 500 students, each with a payroll ID. If I could narrow the focus to just that set of conditions, everything would be simple. We wouldn't have to worry about semesters and all those other numbers. However, I do have to deal with both systems: My local job-site system and the community college's system.

    A little background: My small team of six works for the college. The college has a contract with the site to run two learning labs for their corporation's local employees. When a student completes a "course"--e.g., a four-hour course on Access--he gets four hours' credit on his local training plan. He also gets non-credit "credit" on an official college transcript. The local training plan pays absolutely no attention to semesters. The student has two years to complete 38 hours of work in the lab. However, at the end of every college semester, we enter all the courses that all our students have completed during that time. Here's an example (the college stuff is in <font color=blue>blue</font color=blue>):

    <table border=1><td>HandoutNbr</td><td>Title</td><td><font color=blue>Dept</font color=blue></td><td><font color=blue>CatNbr</td></font color=blue><td><font color=blue>CourseID</font color=blue></td><td><font color=blue>ClassNbr</font color=blue></td><td>118</td><td>Air Pollution</td><td><font color=blue>BISC</font color=blue></td><td><font color=blue>1611</font color=blue></td><td><font color=blue>000525</font color=blue></td><td><font color=blue>00000</font color=blue></td></table>

    None of those numbers changes except <font color=blue>ClassNbr</font color=blue>. The course we call Air Pollution is always CourseID 000525, but if a student completed that course during the Fall 2002 semester, its ClassNbr is 19961; if during the Spring 2003 semester, its ClassNbr is 18831. In day-to-day use, this arrangement causes no problems. However, in our existing non-Access system, when we print a report showing all the coursework a student has completed, courses he took (e.g.) three semesters ago display the current semesters ClassNbr.

    What I need: When the user is keying in course completions in a form and/or when we print a report (a transcript of sorts), I want the form or the report to retrieve the correct ClassNbr. If I key in a date that falls within the Fall 2002 semester, the form should be smart enough to note the date and know that the date belongs to the Fall 2002 semester. My tblSemesters stores those date ranges. I just want to know how to hook all this together and make it work. I hope this is somewhat clearer than before. If not, please be brutally frank--I'm not thin-skinned.

    Thank you!

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

    Re: Select course number by semester (XP)

    Lucas,

    It's not a question of being brutal, it's just that I still feel totally baffled. From your description, it seems that there is no "easy" translation from CourseID to ClassNbr, so you would need a translation table. Do you have a table that resembles the following table more or less (it doesn't have to be exactly like this)?

    <table border=1><td>CourseID</td><td>Year</td><td>Semester</td><td>ClassNbr</td><td align=right>000525</td><td align=right>2002</td><td>Spring</td><td align=right>20091</td><td align=right>000525</td><td align=right>2002</td><td>Fall</td><td align=right>19961</td><td align=right>000525</td><td align=right>2003</td><td>Spring</td><td align=right>18831</td><td align=right>000525</td><td align=right>2003</td><td>Fall</td><td align=right>17701</td><td align=right>000530</td><td align=right>2002</td><td>Spring</td><td align=right>12214</td><td align=right>000530</td><td align=right>2002</td><td>Fall</td><td align=right>13315</td><td align=right>000530</td><td align=right>2003</td><td>Spring</td><td align=right>17792</td><td align=right>000530</td><td align=right>2003</td><td>Fall</td><td align=right>19932</td></table>

    Earlier on, you stated that tblSemesters contains a four-digit semester identifier, the "name" of the semester and the beginning and end dates; I don't see how you could use that to get information like the above, but I may be mistaken.

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

    Re: Select course number by semester (XP)

    Hello Hans! I beg forgiveness for leaving you baffled, but in fact you've answered my question. A translation table is exactly what I need! Thank you, doctor! <img src=/S/doctor.gif border=0 alt=doctor width=25 height=33>

    I think I can use my tblSemesters (with the four-digit semester identifier) as a lookup table to help build the translation table. I think that'll work. You've helped me sort through the clutter!

Posting Permissions

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