Query to show only specific records

[CEV]

I create my Queries via the design view as the SQL view is a little complicated at this point. I know how to get the query to only show records based on the criteria for one column. In the query below, all the fields in tblTraining that has the word completed in it is a yes/no field. Once all those fields are checked yes, I no longer want the record for that employee shown. How can I do this? Thanks. We are a small non-profit and I am one person that does a little of everything and am an expert in nothing.

SELECT tblEmployees.LastName, tblEmployees.FirstName, tblPositions.Department, tblEmployeePositions.PositionNumber, tblEmployees.DateofHire, tblEmployees.DateLeftAgency, tblEmployeePositions.DateStarted, tblTraining.OrienCompleted, tblTraining.RightsCompleted, tblTraining.FireCompleted, tblTraining.UnivPrecCompleted, tblTraining.SwallSafCompleted, tblTraining.CPRCompleted, tblTraining.PosSupCompleted, tblTraining.ManRepCompleted, tblTraining.[1TBCompleted], tblTraining.[2TBCompleted], tblTraining.DeptCheckCompleted, tblTraining.JobDesCompleted, tblTraining.EmergProCompleted, tblTraining.PersCareCompleted, tblTraining.[P&PCompleted], tblTraining.[1stAidCompleted], tblTraining.DietProcCompleted, tblTraining.ShadowCompleted, tblTraining.PersSpecCompleted, tblTraining.SupObsCompleted, tblTraining.MedAdminCompleted, tblTraining.CDS30Completed, tblTraining.CDS90Completed, tblTraining.SafeTransCompleted, tblTraining.MedAdminRenewalDate, tblTraining.PTInterviewCompleted
FROM (tblDepartments INNER JOIN tblPositions ON (tblDepartments.DeptName = tblPositions.Department) AND (tblDepartments.DeptName = tblPositions.Department)) INNER JOIN (tblEmployees INNER JOIN (tblEmployeePositions INNER JOIN tblTraining ON tblEmployeePositions.EmployeeNumber = tblTraining.EmployeeNumber) ON (tblEmployees.EmployeeNumber = tblTraining.EmployeeNumber) AND (tblEmployees.EmployeeNumber = tblEmployeePositions.EmployeeNumber)) ON tblPositions.PositionNumber = tblEmployeePositions.PositionNumber


WHERE (((tblEmployees.DateLeftAgency) Is Null) AND ((tblEmployeePositions.DateStarted)=(SELECT Max(Temp.DateStarted) FROM tblEmployeePositions as Temp WHERE Temp.EmployeeNumber = tblEmployeePositions.EmployeeNumber)))
ORDER BY tblPositions.Department;

[jzwp11]

Based on your query, I think you have a structural issue with your database. It looks like a person can have many training courses but each course is a field in tblTraining: tblTraining.RightsCompleted, tblTraining.FireCompleted, tblTraining.UnivPrecCompleted, tblTraining.SwallSafCompleted, tblTraining.CPRCompleted, tblTraining.PosSupCompleted, tblTraining.ManRepCompleted etc.

If a person can have many training courses then that describes a one-to-many relationship which warrants that the training should be records in a related table. Now, if the same training course can be taken by many people that also describes a one-to-many relationship. When you have 2 one-to-many relationships between the same 2 tables you need a junction table. The following is just a basic structure, the tblEmployeeTraining is the junction table.

tblEmployees
-pkEmployeeID primary key, autonumber
-tblEmployees.LastName
-tblEmployees.FirstName
other employee related fields

tblTrainingCourses
-pkTrainingCourseID primary key, autonumber
-txtTrainingCourseName

tblEmployeeTraining
-pkEmpTrainID primary key, autonumber
-fkEmployeeID foreign key to tblEmployees
-fkTrainingCourseID foreign key to tblTrainingCourses
-dteCompleted (date completed)

[CEV]

Correct, each course is a field in tblTraining. The HR user sees a form with the training names and check boxes next to them. As an employee completes a training the HR user checks the box to mark it has been completed and they can then see easily which ones still need to be completed. I have that list come up in a form, but when they are all checked then I no longer want that employee showing up on the report that is based on this query.

[jzwp11]

Since your database is not normalized, I strongly recommend that you correct the design.

Until such time as that you can get that accomplished, you will have to include each training course completed field in the WHERE clause of the query. If a yes/no field is checked its value is -1 otherwise it is 0.

So if you want to find records where not all of the training records are checked you would need something like this

WHERE (((tblEmployees.DateLeftAgency) Is Null) AND ((tblEmployeePositions.DateStarted)=(SELECT Max(Temp.DateStarted) FROM tblEmployeePositions as Temp WHERE Temp.EmployeeNumber = tblEmployeePositions.EmployeeNumber)))
AND (tblTraining.OrienCompleted=0 OR tblTraining.RightsCompleted=0 OR tblTraining.FireCompleted=0 OR etc.)

[CEV]

Thank you very much!!! I will give that a try for now and then take a look at the design later. I do have a copy of it for playing with so that will help.
Thanks

[jzwp11]

You're welcome. When you are ready to tackle the redesign, I would take a look at this site to get a basic understanding of normalization rules.

I heard that this site has some good tutorials that you might find useful as well.