Single Field Multiple Criteria

[CATC]

Hello,
This is my first time posting to this site and I wanted to thank you for what is accomplished here. This site has helped me through many problems! Please forgive the phrasing of this question if it is not clear. I have struggled with how to ask it for some time.
I have a query that is pulling from multiple tables for an employee training report.
I need a query that returns the employees (EmployeeID) that have completed 2 Training topics (TrainingTopicID). As an example; has the employee taken the written drivers test which has a TrainingTopicID of 11 and has the employee viewed the driver training video which has a TrainigTopicID of 14.
Below is the current SQL for the query. Using the “Or” criteria returns both instances that the criteria has been met, but I just need a list of employees who have attended both trainings.

SELECT tblEmployees.EmployeeID, tblEmployees.CampusID, tblCampus.Campus, [lastname] & "," & " " & [firstname] AS Name, tblTrainingTopic.TrainingTopicID, tblTrainingTopic.TrainingTopic, tblTraining.[Training Date], tblTrainingCategory.TrainingCategory, tblTrainingCategory.TrainingCategoryID, tblEmployees.Active, Year([Training Date]) AS [Year], DateDiff("m",[Training Date],Now()) AS Months, tblEmployees.EmployeeWorkStatusID
FROM (tblCampus INNER JOIN tblEmployees ON tblCampus.CampusID = tblEmployees.CampusID) INNER JOIN (tblTrainingCategory INNER JOIN (tblTrainingTopic INNER JOIN tblTraining ON tblTrainingTopic.[TrainingTopicID] = tblTraining.[Training Topic ID]) ON tblTrainingCategory.TrainingCategoryID = tblTrainingTopic.TrainingCategoryID) ON tblEmployees.EmployeeID = tblTraining.EmployeeID
WHERE (((tblTrainingTopic.TrainingTopicID)=11 Or (tblTrainingTopic.TrainingTopicID)=14) AND ((tblEmployees.Active)=Yes) AND ((DateDiff("m",[Training Date],Now()))<[tblTrainingCategory].[NumberMonths]));

Any help would be greatly appreciated!

Thanks,

 

[jdraw]

If you want Employees who have completed 2 training topics, why do you have all the other stuff Campus, Category, WorkStatus.. in the query.

I suggest you start with the basic that you REALLY need, then expand as necessary. You seem to be making your query more difficult than necessary.

Just work with Employee and Training ( or whatever minimum tables are involved) and get the EmployeeID who has completed 2 topics. The use that EmployeeId to confirm your result.

 

[RainLover]

And use the query grid to build the statement rather than type it our free hand.

 

[CATC]

The original query was filtered by campus with a combo box on a form and by work status to eliminate inactive employees. I took some of the criteria out while playing with the query. I will try your suggestion though and simplify things to work it out.

Thanks

 

[CATC]

Okay I have simplified the query, but the issue remains. The query as shown below returns employees who have training topics 11 or 14. So John Doe who has taken both topics is listed twice. I have tried “And” instead of “Or” and get no returns.
Any suggestions?



SELECT tblEmployees.EmployeeID, [lastname] & "," & " " & [firstname] AS Name, tblTrainingTopic.TrainingTopicID
FROM tblEmployees INNER JOIN (tblTrainingTopic INNER JOIN tblTraining ON tblTrainingTopic.[TrainingTopicID] = tblTraining.[Training Topic ID]) ON tblEmployees.EmployeeID = tblTraining.EmployeeID
WHERE (((tblTrainingTopic.TrainingTopicID)=11 Or (tblTrainingTopic.TrainingTopicID)=14));

 

[nanscombe]

If you want each name displayed only once then you want to add the DISTINCT keyword.

SELECT [B]DISTINCT[/B] tblEmployees.EmployeeID, [lastname] & "," & " " & [firstname] AS Name, tblTrainingTopic.TrainingTopicID
FROM tblEmployees INNER JOIN (tblTrainingTopic INNER JOIN tblTraining ON tblTrainingTopic.[TrainingTopicID] = tblTraining.[Training Topic ID]) ON tblEmployees.EmployeeID = tblTraining.EmployeeID
WHERE (((tblTrainingTopic.TrainingTopicID)=11 Or (tblTrainingTopic.TrainingTopicID)=14));

But wouldn't returning tblTrainingTopic.TrainingTopicID cause multiple occurancies?

 

[CATC]

My mistake, you are correct. I had a date column in the original query that filtered training dates of a certain age. I will add it back and try the DISTINCT keyword .
Thanks!

 

[jdraw]

Could you post a jpg of your tables and relationships?

 

[RainLover]

As I suggested much earlier, build this in the query grid and get your criteria correct.

Just use the many Table. When correct copy and paste the result into your string.

 

[CATC]

I apologize to all, I am having a hard time putting all of this into words and to further complicate things, I am a novice. So I know I am not asking the correct questions and possibly not understanding the suggestions.
Rain: The issue is that I can’t get the criteria correct. Attached is a screen print jpg of the query grid from the many Table with the results below. I hope it is readable. Using 11 Or 14 returns both instances for employee ID 1. But what I want is a list of employee IDs that have completed 11 and 14, not each instance
Jdraw : I have also attached screen print of the relationships and tables.
Thank you all for your patience.

 

[jdraw]

How do you know a Training Topic has been completed? Or is it just that there is a record that that Employee, TrainingTopic and TrainingDate in tblTraining?

To see which EmployeeIds have TrainingTopicId 11 and 14 I'd start with something like this

UNTESTED

Select employeeId 
from trainingTopic
where TrainingTopicId = 11 
and 
EmployeeID IN
(Select employeeId from TrainingTopic
 Where TrainingTopic = 14)

 

[CATC]

Just the fact that there is a record indicates that it is complete. Thank you for the suggestion. I will give it a try.

 

[RainLover]

Make another Query from the first one. Only use the employeeID. Then go to the Menu, I think it is under view, select Group By.

You should now have a list of the employees you want. Again create another query from the second and add the other fields that you want to show.

This query will not be updateable.

 

[nanscombe]

Another possibility to give you a list of employees that have attended courses 11 AND 14.

SELECT tblEmployees.EmployeeID, tblEmployees.lastname, tblEmployees.firstname, T14.[Training Topic ID], T11.[Training Topic ID]
FROM (tblEmployees INNER JOIN tblTraining AS T14 ON tblEmployees.EmployeeID = T14.EmployeeID) INNER JOIN tblTraining AS T11 ON tblEmployees.EmployeeID = T11.EmployeeID
WHERE (((T14.[Training Topic ID])=14) AND ((T11.[Training Topic ID])=11));

 

[CATC]

Rain and nanscombe, I was able to get it to work experimenting with both of your solutions.
Now I just need to step up and understand why they workedJ
Thank you !

 

[jdraw]

I found a spelling error in my SQL. Did you try the query?

 

[CATC]

The spelling error was not a problem. I blame that on my less than desireable naming system. I was unfortunately not able to get it to work.
I was able to get nanscombes suggestion to work and I am now in the process of adding fields to provide more filtering options.

Thank you for your assistance!