Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display info about data not in the database

    [img]/w3timages/icons/question.gif[/img]I need to create a report that shows me a list of completed and not completed skills for each employee. I created a table of the skills that are required
    (tblReqdSkills) to be completed by each employee on a yearly basis (skillID, SkillName). I then created another table (tblSkillsInfo) to enter data about each employee's skills when they have completed them and a completion date (EmployeeID, SkillName, CompletedDate, etc). If each required skill is not entered into the skills info table for each employee, I won't know if every skill for each employee was completed. Is there a way to compare the 2 tables for each employee and determine if any of the necessary skills have not been done?

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Indiana, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display info about data not in the database

    You may not have the structure of your db quite right.

    Have one table with EmployeeID, Name, etc. You may have to have a table for each skill that would contain the EmployeeID, Skill, completion date, etc.

    Then a report based on a query that looks at all the tables could display the skills completed and not completed.

    This is probably what I would do, but I am fairly new at Access. I have had to set up a similar db and this is how it worked for me. HTH.

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

    Re: Display info about data not in the database

    Actually, you need three tables. One for the Employee information (tblEmployee), one for the list of required skills (tblSkill), and one to hold a single record for every completed required skill for each employee (tblEmployeeSkill). The latter is called a join table and need only consist of an EmployeeID and a SkillID, with the two fields making up the primary key of tblEmployeeSkill. Then all you have to do is compare the skills in tblEmployeeSkill with the list in tblSkill to see if any are missing.
    Charlotte

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display info about data not in the database

    My brain has gone to lunch! How do I set up the comparison for this?

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

    Re: Display info about data not in the database

    tblSkills contains a single record for each required skill.
    tblEmployee contains a single record for each employee.
    tblEmployeeSkill contains a single record for each employee with a completed skill of a particular kind.
    So any skills that don't show up in tblEmployeeSkill are missing for that employee.

    You would query the unjoined tables like this:

    <pre>SELECT tblEmployees.EmployeeID, tblSkill.SkillID
    FROM tblEmployees, tblSkill
    WHERE (((tblSkill.SkillID) Not In
    (select [skillID] from tblEmployeeSkill as E
    where E.EmployeeID = tblEmployees.EmployeeID)));</pre>


    That will return a list of all employeeIDs and the skills each employee hasn't completed.
    Charlotte

  6. #6
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display info about data not in the database

    Charlotte,

    Thanks for the help. I can see where I need to follow up my Access class with a Visual Basic class.

    Jim

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

    Re: Display info about data not in the database

    Actually, try a SQL class.[img]/w3timages/icons/grin.gif[/img]
    Charlotte

Posting Permissions

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