Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query design (Access 97)

    Hello Everyone!

    It has been a while since I last visited Woody's Lounge. I have been actively busy with the training part of my job and actually have not worked in Access in a few months. I am now in charge of maintaining our training database and have been asked by our CEO to pull some history data and find some "problem" employees who have missed their yearly training.

    In this database is a listing of all courses, dates, attendance, instructors, locations...etc.

    I need to check each employee's completed courses. I need to extract only the employees who have not had a certain course. For the life of me...I can not get it to work. I have been struggling for 2 days and I am desperate now.

    How can I have a query to examine all the records and extract for me a list of people who have not completed a particular program?


    Thanks in advance! It feels good to be back working in Access however it gets frustrating some times.

    Deborah

  2. #2
    New Lounger
    Join Date
    Apr 2004
    Location
    Reston, Virginia, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query design (Access 97)

    Okay, this is my first post, so be kind. <img src=/S/angel.gif border=0 alt=angel width=15 height=21>
    I'm going to assume you have some field denoting whether or not this course has been completed. Could you possibly use an IIF() statement? Something like IIF([Completion field]=No,[Resource Name]&[Course Name],Null)? Although, I'm not sure if this function is available in 97.
    I hope this helps!

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

    Re: Query design (Access 97)

    Can you tell us a bit more about the structure of the attendance table and how it is related to the employees table and the courses table? How do you determine whether an employee has completed a course?

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query design (Access 97)

    Assuming you have an employees table, a courses tabe, and a course attendances table, I would do it this way.

    * Create a query that finds out who did attend a course, and just lists their employeeID ( or whatever is the key field in the employees table). Let's call that qryAttendees.

    *Create a second query that finds employees not in the first:
    SELECT tblEmployees.*
    FROM tblEmployees
    WHERE (((tblEmployees.EmpID) Not In (Select EmpID from qryAttendees)));

    You can build this in the query grid, by typing Not In (Select EmpID from qryAttendees) into the criteria line under EmpID.

    I attach a demo of this at work.
    Attached Files Attached Files
    Regards
    John



Posting Permissions

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