Query design (Access 97)

Topic

New Reply

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

Reply | Quote

Replies

Okay, this is my first post, so be kind.
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!

Reply | Quote

Okay, this is my first post, so be kind.
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!

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote