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

    'Criterion Numbers' (2000 / 9.0.3821 SR1)

    I work in a learning lab on an industrial site. We offer continuing education courses to a workforce of several hundred laborers. Each employee undergoes an evaluation by his/her supervisor every two years. Before evaluation, the employee must complete XX hours of CE work in our lab. Further, XX hours must be from courses that support one or more of nine evaluation criteria that supervisors use to evaluate an employee's performance. Most courses support just one criterion, but several courses support two or even three criteria.

    My project involves setting up a database to track our students and their time in the learning lab. I've set up a database as follows:

    Table 1: Students
    Table 2: Courses
    Table 3: Criteria

    We need a paper document for each student. We'll use the document to make notations of each visit to the lab. We already have such a document--an unwieldy MS-Word document with a table that serves the purpose. The problem is that we use about seven different variations of this document (different types of employees; employees in different stages of career development). Thus, every time we add a new course, we have to add that course to each of the seven variations--it's time-consuming and tedious. It seems we need to organize this system into a database. With such a system, we can just add the new course(s) to the Courses table; then, when we need to print out a new paper form, the new items will be automatically added to the list.

    I've got several problems with this project, but here's the one that's got me stumped. It's about those criterion numbers. One field on the paper document shows which criterion or criteria a given course supports. What we've been doing is just keying the appropriate number(s) into that cell (e.g., 1, 4, 5). The Criteria table has just two fields: Number (1, 2, ...9 and 0 for electives), and a statement, "Criterion Text," which is simply a description of each criterion. Thus, I don't see the Criteria table as playing a role in building the printed document (or "report") I need to produce.

    The Courses table includes a field for each criterion plus one for electives. I've formatted those 10 fields as Yes/No. Course #56 supports criterion 6, so there's a checkmark under Criterion 6. Course #79 supports Criteria 2, 8 and 9, so I've got three checkmarks.

    So here's the problem: I presume I want to create my paper document with a report, right? OK...how do I set up this report to include a box or cell that displays those three criterion numbers for course #79? If a course supports just one criterion, it's easy: The cell will just display "6" for course #56. The paper document does not have room for 10 cells to display the corresponding checkmarks. But I also don't want those three numbers to appear as "149" (looking like the number "one hundred forty-nine"). They need to be spaced out just a little within the cell.

    That's probably clear as mud, but I'll be grateful for any help. (Did I mention that I'm really, really unschooled in using Access? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

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

    Re: 'Criterion Numbers' (2000 / 9.0.3821 SR1)

    I think you need another table, let's call it CourseCriteria. It would list each of the criteria (as a separate record) that each course satisifes. This new table only needs 2 fields: CourseID and CritieriaNumber. This new table would be on the "many" side of one-to-many relationships with both the Course and Criteria tables.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: 'Criterion Numbers' (2000 / 9.0.3821 SR1)

    Let me see if I understand your reply. In new table "CourseCriteria," I'll have two fields: CourseID and CriteriaNumber. Thus, returning to my example, for CourseID 79, the CriteriaNumber field will show 2 8 9. For CourseID 56, the CriteriaNumber field will show 6. Sounds like that would work. Thanks! I'll give this a try!

    On the other hand: Would there not be some way to have Access collect this information automatically? It seems here I'm keying in "2 8 9"--I'm getting that information by reading from the first table. Seems there oughta be a way to have Access look at #56 and say, "OK...there's a 'Yes' for Criterion 6, so I'll put a 6 right here" and then go to #79 and see three checkmarks and say, "OK...there's a 'Yes' under Criteria 2, 8 and 9, so I'll put '2 8 9' right here."

    (I'll probably have some more questions before I finish with this bear!)

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

    Re: 'Criterion Numbers' (2000 / 9.0.3821 SR1)

    No. You don't key 2 8 9 into the criteria field. You have 3 records for Course #79. The first record has Criteria #2, the 2nd Criteria #8, and the 3rd #9.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: 'Criterion Numbers' (2000 / 9.0.3821 SR1)

    Mark, thanks very much for your prompt response.

    Like I said earlier, I'm kinda green here, so I'm not sure I follow what you're saying. Let's say I'm looking now at this new table, CourseCriteria. The first of two fields displays the course ID numbers only. I've got--for round numbers--200 different course offerings. Ordinarily there would be 200 lines (rows), but some courses (such as #79) support more than one criterion, so I've really got a lot more than just 200 rows. If I understand you correctly, Number 79 (and others like it) appears on three rows...? As in once for each criterion? If that's what you mean, I don't think that approach will work.

    On my report (the paper document I'm trying to create), I need those three numbers to appear on just one line, side-by-side. If my report has to list #79--and others like it--on three lines, my report/document is gonna get rather big and awkward.

    On the document we're now using, we just use a stubby pencil to write in the date a student completes a given course. (There's a blank cell for this purpose.) We also circle the criterion toward which we want to apply that course completion. (A student can't use one course to cover all two or three criteria--you pick which criterion you wanna cover and cirle the number, like 2 or 8 or 9.) We'll continue using the stubby pencil--we have a separate off-the-shelf program we use to track our students' lab work. In this project, I'm trying to automate the way we generate the forms.

    I hope I'm being clear about my goals here, and I really, really appreciate your help, Mark!

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

    Re: 'Criterion Numbers' (2000 / 9.0.3821 SR1)

    You threw a kicker in there when you said that completing one course doesn't mean that the student can apply it to all criteria for that course.

    However, I still think my approach is valid. It does show which criteria are associated with each course. And it also shows the reverse; that is, which courses are associated with which criteria!

    As for data entry, when a student takes a course, you can have a combo box populated with the criteria for that course, such that only 1 can be selected.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: 'Criterion Numbers' (2000 / 9.0.3821 SR1)

    Sorry 'bout the kicker, Mark. You're right about the "data entry" idea--it would work beautifully if we made such selections on-line. However, we will continue to make pencil entries on a paper form. It's the only practical way to do what we do. If we had three or four screens available for students to select their next course, an on-line form would work. The reality is that at any given moment, a lab coordinator has from three to six technophobic students hovering around the desk poring over their paper training records. A student selects a course, we set 'em up, they finish, and we mark the sheet. Later we enter the same information into a digital record-keeping system. I gotta keep this paper record--that's the fact.

    So...I'm still trying to figger out how to make those three criterion numbers appear in one "box" on a report. That's how they appear on the paper document we use now, but it's just a matter of keying them into a single cell in a Word table. That's easy to do, of course, and it works. Maybe I should just leave well enough alone...?

    Nah...I'm too damned persistent to leave it alone! I envision writing three and half pages of complicated code to get this one little box to display the criterion numbers the way I want. I've never written a single line of code--who am I kidding!

    Suggestions, anyone?

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'Criterion Numbers' (2000 / 9.0.3821 SR1)

    If you set up a relational design, as Mark is suggesting, you'll be able to generate your report although it won't be by using a simple query or table as the recordsource. If you set it up the way you have it, it isn't going to be very workable as a database, although it might work as a text editor!

    Reports are generally used for output. They do NOT necessarily look like input screens. You're going to have to decide how you're going to use the *database*, not what your paper form is supposed to look like.
    Charlotte

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

    Re: 'Criterion Numbers' (2000 / 9.0.3821 SR1)

    You could still do this on a report. Something like this:<pre>Course Criteria
    -------------------- --------------------------
    79 (& description) 3 (& description)
    7 ( & description)
    9 ( & description)</pre>

    Granted, it would use up more room, but it would allow you to print the criteria description, which would make it easier to use and prevent mistakes.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: 'Criterion Numbers' (2000 / 9.0.3821 SR1)

    I'm planning to use the database to do several things, but the only purpose I've discussed here is that of creating a specific Access report (a "form" in the conventional, non-Access sense of the word). I've left out any other considerations to avoid confusion. Perhaps it will help if I were to be more specific as to the nature of the paper document I want to generate.

    The report header contains a label and spaces to write in a student's name and other information. The page header would display the following column headings from left to right:

    Course title
    Course number
    Medium (video, CBT, laser)
    Credit (in clock hours)
    Criterion number(s)
    (Then there are four more columns for stubby-pencil entries such as the date the student completed a given course.)

    The above items have a "record source" in one or more of my tables. I've gotten all of these fields to work the way I want...except the last one. I'm looking right now at a paper copy of the "form" I'm trying to create, and everything looks just great except for "Criterion numbers." What I've got for the moment is 10 fields (one for each criterion plus "electives"). All I want to display in just one "field" or cell is the answer to the question, "What criterion or criteria does this course support?" If the answer is just one criterion--#6, e.g.--then I want to display the number 6. If the answer is more than one criterion--e.g., 2, 8, and 9--then I want to display those three numbers. So my only need for the moment is to find a way to do as I've described.

    Perhaps I'm trying to do something that can't be done--not in an automatic way, that is, via Access. It just seems that Access is such a powerhouse, there oughta be a way to get the results I need, certainly with a little help from VBA. A "relational design" is exactly what I want to use. I haven't set up relationships yet, in part because I'm still trying to understand that concept as well. Forgive my long-winded post, please--just trying to communicate. Thank you, Mark and Charlotte!

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

    Re: 'Criterion Numbers' (2000 / 9.0.3821 SR1)

    For what it's worth, I thought I'd let the forum know how I solved my problem. You'll recall that my table 'Courses' included a Yes/No field for each "criterion." Most courses apply to a single criterion; a good number of courses apply to two or even three criteria. I asked how I might get a report to display only the criterion numbers from the 'Yes' fields. I did NOT want to waste space having to carry all ten criterion fields on the report.

    Here's how I solved the problem: I noted that no single course applies to more than three criteria. So I dumped the Yes/No fields from the table and replaced them with just three text fields: CritA, CritB, CritC. Course #55 supports only Criterion 6, so the number 6 goes into the CritA field. Course #89 supports Criteria 2, 8, and 9; those numbers display under CritA, B, and C, respectively. I put those three fields onto my report, and the criterion numbers display exactly as I'd planned.

    Thanks to everyone who responded.

Posting Permissions

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