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

    Combine sources for report (XP)

    One of my database projects publishes a report that lists the courses that a particular employee/student must complete. This is an industrial training project where the students are manufacturing employees. We have about 270 courses in our "catalog." Each job has a list of courses that is a subset of the catalog; each job has a somewhat different list, although there's a lot of overlap. We call the report a "training plan." Each training plan displays the student's name, payroll ID, and job title in the report header. The detail section displays the list of courses for that employee's job.

    A query drives the report. The query draws from the tblStudents table and from a table (tblProgramsRequiredCourses) that is in the middle of a many-to-many relationship between two other tables: tblCourses and tblPrograms. When a user clicks a command button to print a training plan, he gets a prompt to enter the payroll ID (PID) for the student.

    Now here's a further complication: A significant number of students hold more than one job title. In one sector of our site, the typical employee starts out as a "spinner." Later, when the employee is ready, he or she will progress and take on an additional role. Some employees hold three, four or even five separate roles! Each role has its own set of courses, but those sets have much in common. What we're doing for now is to print a training plan for each of a worker's job titles. One employee that I work with has three training plans. On the second and third training plans, we've lined through the duplicate courses. To get those three reports, we have to reset the employee's job title twice. That gets the job done, but what a nuisance!

    How would you suggest I streamline this process? Obviously, what we need is a way to produce a single training plan that combines the courses from each job title and eliminates the duplicate courses. We thought about simply setting up a separate set of courses for each possible combination. That might work if there were a simple line of progression. If an employee starts out with Job #1, then adds #2, #3 and so on, it would be relatively simple. However, one employee may hold Jobs 1, 2, and 4; another 1, 3, and 5; another 1, 2, 4, and 5.... There are many possible combinations. Yikes!

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

    Re: Combine sources for report (XP)

    From the sentence "To get those three reports, we have to reset the employee's job title twice", I gather that you have a job title field in the tblStudents table. Since one student (employee) can have more than one job title, you have another many-to-many relationship, and you need another intermediate table to implement it: a table, say tblStudentJobs (of course, you may give it another name) with at least two fields: a field StudentID that is linked to tblStudents and a field JobID that is linked to a table with details about the job titles. The StudentID and JobID fields are not unique by themselves, but the combination of the two fields would be the primary key of this table.

    Having this intermediate table enables you to collect all courses for a particular student into one report.

  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: Combine sources for report (XP)

    I uncertain why you would want to eliminate the so-called duplicate courses. I'd like to see the report in order by Employee and Job Title. That way, I can what courses the employee should be taking for each job title, and which ones have already been taken. For example, if 1 particular job title requires 5 courses, I'd like to see all 5 courses listed showing which ones were already completed (even if already shown for another job). I'd think this would be preferable to only seeing 2 courses listed for that job (which you know isn't the number actually required, but then you have to guess why the others aren't listed!).
    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: Combine sources for report (XP)

    "Another linking table." Of course! That should do it! Of course, this brings up another question:

    In a recent post (<post#=360005>post 360005</post#>) I asked about the alleged mischief that lookup fields can cause. As I prepare to create another linking table, I wonder how best to construct it. Were I to follow my usual approach, I would create the two fields with the Lookup Wizard to establish links to the respective tables. Then I'd select both fields and make them both the Primary Key. Is there another way to set up these two fields...besides using the Lookup Wizard? Which approach would be better?

    Again, thank you for your advice!

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

    Re: Combine sources for report (XP)

    That's a very good point

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

    Re: Combine sources for report (XP)

    As I wrote in the other thread, I wouldn't worry about it too much. If the Lookup Wizard helps you to obtain your goal, use it without qualms. The numbers of records you are dealing with are not very large, so you won't suffer a noticeable performance hit.

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

    Re: Combine sources for report (XP)

    OK, I won't worry about it, but here's a question for you: Define "large," as in number of records. I'm not saying I have a large number of records, but I do have:

    About 700 students
    About 270-plus courses
    A front end that's grown to a bit over 2.0 MB
    A backend that's now about 2.6 MB

    I'm just wondering at what point a guy needs to be concerned about "performance hits." It's working just fine now.

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

    Re: Combine sources for report (XP)

    With "large" I would mean hundreds of thousands of records. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    The size of your tables and of your databases are still relatively small.

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

    Re: Combine sources for report (XP)

    OK...now I know. Just had to ask...!

    I went ahead with building the table with the Lookup Wizard, and I built in two fields: PID (payroll ID) from tblStudents; and ProgramID from tblPrograms. I call the new table (as you did) tblStudentPrograms. Then I eagerly went to the empty table and tried to put some stuff in it. I entered a payroll ID that I know is good (it shows up in the combobox list)...and don't you know that the instant I tabbed to the ProgramID field, the PID I'd just entered disappeared! I already knew that the Lookup Wizard likes to hide the Primary Key. Well, PID is the Primary Key for tblStudents! At least, that's what I was thinking, and I suppose that's why the PID runs and hides...? However, upon closer inspection, I found that I'd neglected to identify PID or anything else as the Primary Key for this table! The table has no Primary Key!

    So here's a whole new set of questions: Should I make PID the Primary Key? Should I add an AutoNumber field as the Primary Key? What do I risk (or what will I gain) with either approach? PID should work as a Primary Key: Every employee has a unique PID

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

    Re: Combine sources for report (XP)

    Try making the record source of the report a query that returns the desired fields; then set its Unique Values property to True.

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

    Re: Combine sources for report (XP)

    Thank you, Hans! One more question: I notice the Properties sheet offers Unique Values and Unique Records. "Help" describes these two related properties, and I understand that only one of these properties can be set to Yes. The explanation left me a little uncertain about the difference between the two. Can you explain the difference?

    Oh...one more thing. I had a question that got buried in the rubble of my previous post. If this were your project, would you use the PID (payroll ID) field as the Primary Key for the Students table, or would you add an AutoNumber field? What might be the advantage of one approach over the other? Thank you!

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

    Re: Combine sources for report (XP)

    Lucas,

    See <post#=360611>post 360611</post#> for a feeble attempt to explain the difference between Unique Values and Unique Records. (I don't think that the poster was quite satisfied with the answer...)

    Since you already have a field PID that is guaranteed to be filled in for each student record and to be unique, I would use that as Primary Key. Make sure that you enforce relational integrity and specify cascading updates for the relationships involving the PID field. An AutoNumber field is very handy if you have a table without a "natural" unique key, but you don't really need an AutoNumber field here.

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

    Re: Combine sources for report (XP)

    I've tried both Unique Values and Unique Records, but I'm still seeing duplicates. If it'll help, here's the SQL view of the query (UniqueValues = Yes):

    SELECT DISTINCT tblStudents.PID, tblStudents.EmplID, tblStudents.FirstName, tblStudents.MiddleName, tblStudents.LastName, tblStudents.Nickname, tblStudentPrograms.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, [Course] & IIf(IsNull([Repeat]),""," (must repeat every " & [Repeat] & " years)") AS CourseText
    FROM tblStudents INNER JOIN ((tblPrograms INNER JOIN (tlkpMediaTypes INNER JOIN (tblCourses INNER JOIN tblProgramRequiredCourses ON tblCourses.CourseKey = tblProgramRequiredCourses.CourseKey) ON tlkpMediaTypes.MediumID = tblCourses.Medium) ON tblPrograms.ProgramID = tblProgramRequiredCourses.ProgramID) INNER JOIN tblStudentPrograms ON tblPrograms.ProgramID = tblStudentPrograms.ProgramID) ON tblStudents.PID = tblStudentPrograms.PID
    WHERE (((tblStudents.PID)=[Enter PID]));

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

    Re: Combine sources for report (XP)

    If you open this query in datasheet view, there MUST be at least one difference between two subsequent records. If a query with SELECT DISTINCT returns two or more absolutely identical records, I don't understand what is happening. Perhaps there is a difference in a field you don't need in the report?

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

    Re: Combine sources for report (XP)

    I'm looking now at two records for one student in datasheet view. These records are identical in every respect, except for the fields that identify the student's training programs. This student has three training programs: 27, 33, and 37. In this case, programs 33 and 37 both require that she take one particular course. Therefore, the title for that course appears in both records. Seems to me that if I were to remove the fields that display non-unique data, I'd defeat the entire purpose of this query.

    Am I asking too much of this query? I'll explain: I'm using this query to furnish absolutely everything to the report ("training plan"). In the Report Header, I have a text box that displays the student's training program(s). Therefore, I need the query to output this student's three training programs. The Detail section displays the courses that the collection of training programs requires of the student, but I don't need to display the same course title two or three times. Maybe I need another approach...?

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
  •