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

    'Done' courses (2000/9.0.3821 SR1)

    I run a learning lab on an industrial site. My students (plant employees) are required to complete 38 clock hours of lab work every two years. Students can choose courses from a curriculum of about 230 courses. Students typically take about 20 courses to get their 38 hours.

    My Access database includes a report that displays all the courses available. We print this report for each student, and we use the paper copy to mark off courses as the student completes them. When the student completes his 38 hours, he starts over with the next 38-hour round and we print a new report.

    Here's where I need help. When the student begins his second round, the "rules" do not allow him to repeat any of the courses he took during round one. In the past, we printed the new report and lined through the courses he can't take in round two. Now we print the round two report with those off-limits courses not included. To do this, I added a yes/no field ("Done") to the table, tblCourses. I use the list of courses completed in round one and mark them as "Done" in the table. A query recognizes the Yes responses and deletes them for the report.

    Of course, this is still pretty crude. After I've printed the new report for a student, I have to go back to the table and uncheck all the Yes answers before I go on to another student. So I'm looking for a way to automate this process as much as possible. We use Pinnacle Learning Manager (in addition to everything else), which speaks SQL. From PLM, I can export a list of an individual student's completions. What I can't figure out is a way to use that exported list of completed courses to eventually generate the Access report I mentioned earlier. I'll be grateful for any suggestions.

  2. #2
    eLar
    Guest

    Re: 'Done' courses (2000/9.0.3821 SR1)

    Create a query to perform an outer join between your list of available courses and the employee's completed courses and select the resulting records where the completion information is blank. That should give you the list you want.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: 'Done' courses (2000/9.0.3821 SR1)

    Is tblCourses a list of all courses available or does it contain which courses each Employee took. That is, it is on the "many" side of a one-to-many relationship with tblEmployees.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: 'Done' courses (2000/9.0.3821 SR1)

    tblCourses is a list of all courses available to any given student. So yes, this table is on the "many" side.

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

    Re: 'Done' courses (2000/9.0.3821 SR1)

    Thank you. Here's what I've done so far:
    1. I created a new table importing one student's completion record. Now I have a table (tbl_[Student]Completions that contains one student's list of completions, with fields "Title" (the course name), "CourseNbr" and "CompletionDate."
    2. I've built a query using both tables as sources. From tblCourses, I've used CourseNbr and Title; from tbl_[Student]Completions, I've used just the completion date.

    Now what? That is, how do I get from here to producing a report that displays all available courses EXCEPT the ones that appear in this query?

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

    Re: 'Done' courses (2000/9.0.3821 SR1)

    It is easiest to use the Find Unmatched Records wizard to create the query.

    When you create a new query, you see several choices. One of them is the Find Unmatched Records Wizard.
    In the first step, select tblCourses.
    In the second step, select tbl_xxxCompletions.
    In the third step, select the CourseNbr field on both sides and click on the link button.
    In the fourth step, select any other fields you like.
    In the final step, change the name if you like and click Finish.

    You can adapt this query to your needs.

    BTW, I think it would be a better idea to create one large table tblCompletions, with StudentID, CourseNbr and CompletionDate. In that case, you don't need to create a new table for each student.

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

    Re: 'Done' courses (2000/9.0.3821 SR1)

    Great idea, Hans! Thanks!

  8. #8
    eLar
    Guest

    Re: 'Done' courses (2000/9.0.3821 SR1)

    You need to make sure the Join type is set for an outer join (In the Query Design view double click the line linking the two data source and specify the appropriate relationship so that you get all records from tblCourses and only those records from tblStudentCompletions that match). Pick any field from tblStudentCompletions and add the criteria "Is Null" (without the quotes). When you run the query you should get the results you want.

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

    Re: 'Done' courses (2000/9.0.3821 SR1)

    [...specify the appropriate relationship so that you get all records from tblCourses and only those records from tblStudentCompletions that match.]

    In this case, every record in tblStudentCompletions will match a record in tblCourses. Say a student completed 25 courses out of the 230 available--every one of those course he completed appears in the other table. You won't see anything in the student's completions list that does not appear in tblCourses. Does that matter?

    Thanks to everyone for your contributions. I'll have to apply all this information when I return to work Monday.

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

    Re: 'Done' courses (2000/9.0.3821 SR1)

    What eLar and Hans have said is essentially the same thing.

    What you want is a list of courses that the student has not done, am I right?

    eLar has explained the type of query you need and Hans has spelt out how to setup such a query.

    Pat

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

    Re: 'Done' courses (2000/9.0.3821 SR1)

    You are precisely correct. Now I see what you--and they--mean. Thanks!

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

    Re: 'Done' courses (2000/9.0.3821 SR1)

    I've taken Hans's recommendation and set up a table, tblCompletions, in which to store information about student completions. The table has three fields: SSN (format: text), CourseID (format: text), and CompletionDate (format: Date/Time--Short Date). I've made the SSN field the primary key.

    Now I've hit a snag trying to import information out of Pinnacle Learning Manager (cited earlier). I export a student's record out of PLM as a .csv. That file includes information I do not need, so in Excel I dump the fields I don't want and reduce it to exactly the three fields I need. I did not include a header row--not necessary. The student's SSN appears with every course ID/completion date. I save the .csv file and close it.

    Now I return to Access to import the file into tblCompletions. The two files (the .csv file and tblCompletions) are set up so as to display the three data elements (SSN, CourseID, date) in the same order. But...the import attempt fails. Three times I've gotten the same error message:

    "Access was unable to append all the data to the table.... Either the records you pasted contain primary key values that already exist in the destination table, or they violate referential integrity rules for a relationship defined between tables."

    I've been into the "Advanced" window to make sure the imported fields are formatted to match the formatting in the destination fields. That didn't help. The error message gives me the choice of continuing or not--When I say Yes, tblCompletions displays only the first record--the other 37 records were "lost." What have I overlooked?

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

    Re: 'Done' courses (2000/9.0.3821 SR1)

    One student can complete several courses, so SSN can't be the primary key. The combination of a student and a course should be unique, so I suggest adding CourseID to the primary key. In the index window, add CourseID below SSN without entering a key name in the left hand box.

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

    Re: 'Done' courses (2000/9.0.3821 SR1)

    Hans et al: Much to my delight, I've succeeded in making a query using 'Find Unmatched Records.' The resulting query shows me exactly what I want! Thanks for the help!

    But I've hit another snag: The next step is to create a report to display (on paper) the courses that remain for the student to select during his or her next round of training. (S/he cannot repeat a course taken during the immediately previous round, so the new list must display all courses minus those the student took during the previous round.) Like I said, the query looks exactly like what I want. The next problem occurred when I tried to put the query into the report. I opened the report (we call it a training plan) and changed its data source to the query I just created. When I switch from design view to print preview, Access displays a series of boxes asking me to ENTER PARAMETER VALUE for (course) Title, Medium, Credit, and other fields. What have I overlooked here?

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

    Re: 'Done' courses (2000/9.0.3821 SR1)

    This probably means that the Title, Medium etc. fields are not in the Find Unmatched Records query.

    You can do two things:
    <UL><LI>Open the query in design view and add the "missing" fields, or
    <LI>Create a copy of the report, and remove the controls bound to the fields that are missing in the Find Unmatched Records query.[/list]

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
  •