Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231

    Unanswered: head.too.thick. (Which classes am I missing?)

    I have the feeling that this should be simple, but I just can't see it clearly.

    Here's the skinny:
    Employees take training classes. they gain "skills" from taking these courses.

    So if the skillset called Horseback Riding is made up of 4 classes (maybe Bridle Care, Mechanics of Balance, Equestrian Psychology and Kick Deflection), and Person A takes just the bridle care class, he is considered skilled in horseback riding. I can handle all of that. What I can't seem to wrap my head around is how to tell what the other courses he needs to complete that skillset are in a query.

    My tables are:
    Skills table, which defines the 95 different skills.
    SkillstoClass table, which holds SkillID relates each skill to the classes.
    Classes table, which defines the 500+ classes.
    Employees table, which holds employee information
    EmployeeClass table, which hold the EmpID and ClassID showing which classes they've taken.

    Have I made this too complex? Any suggestions as to how I should approach this? I've got a good idea of how to do it, it just doesn't work, so I won't post my failed logic and confuse things. Thanks for reading. Have a great Tuesday.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Nowhere in your current design do you store which individual skills the employee has, only the class...

    So all you can tell is whether someone has that class or not, right?
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    well, i have a query (qryEmployeeSkills) which uses the EmployeeClass table, and joins it to the SkillstoClass table on the ClassID. The query returns the DISTINCT EmpID and SkillID. So that matches tells what skills each employee has, based on which classes they've taken.

    Not trying to jump the gun with posting my failed logic, but the idea was to Left Join that query to the SkillsToClass table on SkillID (qryClassesNeeded). That should give me a list of empIDs and all the classIDs they need to complete the skillsets they already have classes in.

    Next step would be to have a query listing EmpID and ClassID (qryEmpClasses)

    Then, I'd Left Join qryClassesNeeded to qryEmpClasses on ClassID where qryEmpClasses.ClassID Is Null.
    Last edited by jmahaffie; 07-15-08 at 10:49.

  4. #4
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    Nevermind...I went back and made some changes and it works perfectly. Takes several queries to get the info presented like i want it, but it works well. Thanks, georgev, for letting me talk through it. Thing is, it's not my database. A guy needed some help with one yesterday, and I talked though it with him for about 2 hours and we couldn't get it right. So derned simple. Thanks again.

Posting Permissions

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