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

    Special field (XP)

    I'm working on a report that we call a "training plan." The report uses a query to create a three- to four-page list of the courses a student must choose from to complete a set of requirements. (These students are factory workers, not students in the traditional sense.) One subset of this student population must repeat designated courses every four years. I need a way to identify those courses on the appropriate training plans.

    I have tables for courses, students, and what I call "programs." To simplify this setup, let's say I have programs for Production, Maintenance, Shipping, and Training. The people in Training are the only ones who have to repeat any courses at certain intervals (so far). To mark the courses these trainers have to repeat, I could create separate and almost duplicate entries for those courses (e.g., "Fundamentals of Business" for the non-trainers; "Fundamentals of Business (repeat every four years)" for the trainers. But that approach seems cludgy and inflexible.

    Here's what I've done so far: I have a junction table, tblProgramRequiredCourses, that facilitates the many-to-many relationship between tblPrograms and tblCourses. The datasheet displays the program (e.g., Trainer) and the courses that the program requires, something like this:

    <table border=1><td>Program</td><td>Course</td><td>Repeat</td><td>Trainer</td><td>Hand tools</td><td>4</td></table>

    The "Repeat" field displays the number of years that can elapse before the student has to repeat the course. What I'd like is for the query or something to pick up that number and then append a short statement to the end of the "Course" (title) field. If Hand Tools appears on the training plan for a Trainer, the course title would display: "Hand Tools (must repeat every 4 years)."

    Trouble is, I can't figure out how to make that work. Suggestions, anyone? (By the way, another idea I considered is to simply add a column to the report to display a message about the requirement to repeat a given course. Trouble is, by far the largest number of training plans will not need such a field, and those reports will then have a big gap. We print each student's report on standard 8-1/2 X 11 paper, so I don't have that kind of room to waste.) Thanks for any ideas you can offer!

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

    Re: Special field (XP)

    This is off the top of my head, since I don't know the design of the record source of the report. You wrote that it is a query. Open this query in design view, and add tblProgramRequiredCourses. Join it to the existing tables on (I presume something like) ProgramID and CourseID; double click these joins and click the option to display ALL records from the other tables and only those records from tblProgramRequiredCourses. Add the Repeat field to the query grid.

    Try to open the query in datasheet view. You may get an error message about invalid outer joins; in that case, we would have to take a closer look at the existing query. If it works, you will see that most records have a blank value for Repeat. Close and save the query.

    On the report, select the text box bound to the CourseTitle field. If it is also named CourseTitle, change its name. Then set its Control Source to something like (again, I don't know the exact field names)<pre>=[CourseTitle] & IIf(IsNull([Repeat]),""," (must repeat every " & [Repeat] & " years)"</pre>

    HTH

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

    Re: Special field (XP)

    Hello, Hans. I'm afraid I got the dreaded error message: "The SQL statement could not be executed because it contains ambiguous outer joins." <sigh....> So, without further ado, here's the SQL statement as it reads after I modified the query:

    <font face="Georgia">SELECT tblStudents.PID, tblStudents.EmplID, tblStudents.FirstName, tblStudents.MiddleName, tblStudents.LastName, tblStudents.Nickname, tblPrograms.ProgramID, tblPrograms.ProgramArea, tblPrograms.ProgramDescription, tblPrograms.JSEP_Program, tblStudents.Next_Assess, Nz([JSEP_Program],"No JSEP program") AS Rev, tblCourses.HandoutNbr, tblCourses.CourseTitle, tblCourses.Credit, tlkpMediaTypes.MediaAbbrev, [HandoutNbr] & " - " & [CourseTitle] AS Course, tblProgramRequiredCourses.Priority, tblProgramRequiredCourses.Repeat
    FROM ((tblPrograms INNER JOIN tblProgramRequiredCourses ON tblPrograms.ProgramID = tblProgramRequiredCourses.ProgramID) INNER JOIN tblStudents ON tblPrograms.ProgramID = tblStudents.ProgramID) LEFT JOIN (tlkpMediaTypes INNER JOIN tblCourses ON tlkpMediaTypes.MediumID = tblCourses.Medium) ON tblProgramRequiredCourses.CourseKey = tblCourses.CourseKey
    WHERE (((tblStudents.PID)=[Enter PID]));</font face=georgia>


    I've read thru the SQL statement, but my eyes can't see where the problem lies. I'll be happy to any other information you need. Thank you very much for looking at this!

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

    Re: Special field (XP)

    Lucas,

    You may not need the outer join after all, but I wonder how the connection between programs and courses is implemented. Do you have a record in tblProgramRequiredCourses for each combination, with Repeat left blank if there is no obligation to repeat the course? In that case, the query should work with inner joins. If there is another intermediary table involved, tell us more. (I know, you have probably done so before, but the thread would become incomprehensible if we took that for granted.)

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

    Re: Special field (XP)

    Hans: I made some changes to the query, and the SQL statement looks different now. I just read your last reply, and yes: I do have a field in tblProgramRequiredCourses that displays the interval for repeating a course. And yes, this table looks just as you describe. The problem I have now is in the report, as you'll see below.

    <font face="Georgia">SELECT tblStudents.PID, tblStudents.EmplID, tblStudents.FirstName, tblStudents.MiddleName, tblStudents.LastName, tblStudents.Nickname, tblPrograms.ProgramID, tblPrograms.ProgramArea, tblPrograms.ProgramDescription, tblPrograms.JSEP_Program, tblStudents.Next_Assess, Nz([JSEP_Program],"No JSEP program") AS Rev, tblCourses.HandoutNbr, tblCourses.CourseTitle, tblCourses.Credit, tlkpMediaTypes.MediaAbbrev, [HandoutNbr] & " - " & [CourseTitle] AS Course, tblProgramRequiredCourses.Priority, tblProgramRequiredCourses.Repeat
    FROM tlkpMediaTypes INNER JOIN ((tblPrograms INNER JOIN (tblCourses INNER JOIN tblProgramRequiredCourses ON tblCourses.CourseKey = tblProgramRequiredCourses.CourseKey) ON tblPrograms.ProgramID = tblProgramRequiredCourses.ProgramID) INNER JOIN tblStudents ON tblPrograms.ProgramID = tblStudents.ProgramID) ON tlkpMediaTypes.MediumID = tblCourses.Medium
    WHERE (((tblStudents.PID)=[Enter PID]));
    </font face=georgia>

    I did NOT modify the joins as you described

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

    Re: Special field (XP)

    Try removing qryTrainingPlan!

    =[Course] & IIf(IsNull([Repeat]),""," (must repeat every " & [Repeat] & " years)")

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

    Re: Special field (XP)

    Sorry, but that did not fix it. I failed to mention earlier that "#Error" appears on every line where the course number and title should appear, and it even appears in the appropriate color. (You and others helped me a few days ago to write code to display course titles in black, red or blue

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

    Re: Special field (XP)

    1. Either you are entering a ProgramId in the students table that is not present in the programs table, or the relationship is set up incorrectly. As you say, hard to determine from a distance.

    2. About the #Error: what happens if you put a calculated field

    CourseText: [Course] & IIf(IsNull([Repeat]),""," (must repeat every " & [Repeat] & " years)")

    in the query? Does it display the correct text, or an error?

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

    Re: Special field (XP)

    Wow, 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
  •