Help with Joining, multiple parameters, returning more than 1 reco

[Guest]

To summarize, I have an Employee Query (all queries pulled directly from
tables, where applicable), a Skills Query, and an Employee-Skills Query, that
constitutes all of the skills that each employee has (ie. Employee 1 - Skill
3, Employee 1 - Skill 7, Employee 2 - Skill 1, Employee 2 - Skill 3, Employee
2 - Skill 7, etc). When querying the database I want to be able to retrieve
only Employees that have ALL of whatever Skills are entered by the searcher.
The best I can do so far is have it return all Employees that have, for
instance, either Skill 3 OR Skill 7, not only Employees that have both Skill
3 AND Skill 7.

Here is my code so far (simplified, but still functional):

SELECT [Employee Query].LastName, [Employee Query].FirstName, [Skills
Query].[Technical Skill]
FROM [Employee Query], [Employee-Skills], [Skills Query]
WHERE ((([Skills Query].[Technical Skill])=[Enter First Skill:] Or ([Skills
Query].[Technical Skill])=[Enter Second Skill:]) **using 'AND' instead of
'Or' here, which seemed intuitive to me, doesn't work**
AND (([Employee Query].EmployeeID)=([Employee-Skills].[EmployeeID])));
AND ((Employee-Skills.SkillID)=([Skills Query].[SkillID])) **joins all my
tables together**

I looked through some old responses and found mention of 'InStr' but can't
quite figure out how to use it appropriately.

Can someone please help?

 

[Allen Browne]

The solution will involve using subqueries in the WHERE clause.

It will be best if you can run this against the tables rather than the
queries. You will end up with something like this:

SELECT EmployeeId, LastName, FirstName FROM tblEmployee
WHERE EXISTS
(SELECT EmployeeId FROM tblEmployeeSkill
WHERE tblEmployeeSkill.EmployeeId = tblEmployee.EmployeeId
AND tblEmployeeSkill.SkillId = 3)
AND EXISTS
(SELECT EmployeeId FROM tblEmployeeSkill
WHERE tblEmployeeSkill.EmployeeId = tblEmployee.EmployeeId
AND tblEmployeeSkill.SkillId = 7);

If subqueries are new, see:
How to Create and Use Subqueries
at
http://support.microsoft.com/?id=209066

 

[Guest]

In your example what is contained in tblEmployeeSkill? My equivalent is just
comprised of EmployeeIDs and SkillIDs, as two foreign keys . It looks
something like this:

EmployeeID SkillID
1 2
1 3
1 7
2 2
2 15
3 2
3 7
3 12
etc.

I have done my best to adapt your code to my variable names, am referencing
the tables directly instead of the queries and have substituted
"tblEmployeeSkill.SkillId = [Enter First Skill:]" for
"tblEmployeeSkill.SkillId = 3". The first pop up asks me to enter
tblEmployeeSkill.EmployeeID (and I don't know why, as it IS the proper name
for the variable in that table, and everything is spelled right). The next
two pop ups ask me to input the first and second skills, but no matter what I
enter it is still pulling up a completely blank query.

I am decent at working with the code and I can't see my problem(s). Any
further ideas?

Thanks,

The solution will involve using subqueries in the WHERE clause.

It will be best if you can run this against the tables rather than the
queries. You will end up with something like this:

SELECT EmployeeId, LastName, FirstName FROM tblEmployee
WHERE EXISTS
(SELECT EmployeeId FROM tblEmployeeSkill
WHERE tblEmployeeSkill.EmployeeId = tblEmployee.EmployeeId
AND tblEmployeeSkill.SkillId = 3)
AND EXISTS
(SELECT EmployeeId FROM tblEmployeeSkill
WHERE tblEmployeeSkill.EmployeeId = tblEmployee.EmployeeId
AND tblEmployeeSkill.SkillId = 7);

If subqueries are new, see:
How to Create and Use Subqueries
at
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

To summarize, I have an Employee Query (all queries pulled directly from
tables, where applicable), a Skills Query, and an Employee-Skills Query,
that
constitutes all of the skills that each employee has (ie. Employee 1 -
Skill
3, Employee 1 - Skill 7, Employee 2 - Skill 1, Employee 2 - Skill 3,
Employee
2 - Skill 7, etc). When querying the database I want to be able to
retrieve
only Employees that have ALL of whatever Skills are entered by the
searcher.
The best I can do so far is have it return all Employees that have, for
instance, either Skill 3 OR Skill 7, not only Employees that have both
Skill
3 AND Skill 7.

Here is my code so far (simplified, but still functional):

SELECT [Employee Query].LastName, [Employee Query].FirstName, [Skills
Query].[Technical Skill]
FROM [Employee Query], [Employee-Skills], [Skills Query]
WHERE ((([Skills Query].[Technical Skill])=[Enter First Skill:] Or
([Skills
Query].[Technical Skill])=[Enter Second Skill:]) **using 'AND' instead of
'Or' here, which seemed intuitive to me, doesn't work**
AND (([Employee Query].EmployeeID)=([Employee-Skills].[EmployeeID])));
AND ((Employee-Skills.SkillID)=([Skills Query].[SkillID])) **joins all my
tables together**

I looked through some old responses and found mention of 'InStr' but can't
quite figure out how to use it appropriately.

Can someone please help?

 

[Allen Browne]

Yes, tblEmployeeSkill is made up of 2 foreign keys.

Try declaring the parameters in the main query.
In query design view, choose Parameters on the Query menu.
In the dialog enter 2 rows, e.g.:
[Enter First Skill:] Long

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

In your example what is contained in tblEmployeeSkill? My equivalent is
just
comprised of EmployeeIDs and SkillIDs, as two foreign keys . It looks
something like this:

EmployeeID SkillID
1 2
1 3
1 7
2 2
2 15
3 2
3 7
3 12
etc.

I have done my best to adapt your code to my variable names, am
referencing
the tables directly instead of the queries and have substituted
"tblEmployeeSkill.SkillId = [Enter First Skill:]" for
"tblEmployeeSkill.SkillId = 3". The first pop up asks me to enter
tblEmployeeSkill.EmployeeID (and I don't know why, as it IS the proper
name
for the variable in that table, and everything is spelled right). The next
two pop ups ask me to input the first and second skills, but no matter
what I
enter it is still pulling up a completely blank query.

I am decent at working with the code and I can't see my problem(s). Any
further ideas?

Thanks,

The solution will involve using subqueries in the WHERE clause.

It will be best if you can run this against the tables rather than the
queries. You will end up with something like this:

SELECT EmployeeId, LastName, FirstName FROM tblEmployee
WHERE EXISTS
(SELECT EmployeeId FROM tblEmployeeSkill
WHERE tblEmployeeSkill.EmployeeId = tblEmployee.EmployeeId
AND tblEmployeeSkill.SkillId = 3)
AND EXISTS
(SELECT EmployeeId FROM tblEmployeeSkill
WHERE tblEmployeeSkill.EmployeeId = tblEmployee.EmployeeId
AND tblEmployeeSkill.SkillId = 7);

If subqueries are new, see:
How to Create and Use Subqueries
at
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

To summarize, I have an Employee Query (all queries pulled directly
from
tables, where applicable), a Skills Query, and an Employee-Skills
Query,
that
constitutes all of the skills that each employee has (ie. Employee 1 -
Skill
3, Employee 1 - Skill 7, Employee 2 - Skill 1, Employee 2 - Skill 3,
Employee
2 - Skill 7, etc). When querying the database I want to be able to
retrieve
only Employees that have ALL of whatever Skills are entered by the
searcher.
The best I can do so far is have it return all Employees that have, for
instance, either Skill 3 OR Skill 7, not only Employees that have both
Skill
3 AND Skill 7.

Here is my code so far (simplified, but still functional):

SELECT [Employee Query].LastName, [Employee Query].FirstName, [Skills
Query].[Technical Skill]
FROM [Employee Query], [Employee-Skills], [Skills Query]
WHERE ((([Skills Query].[Technical Skill])=[Enter First Skill:] Or
([Skills
Query].[Technical Skill])=[Enter Second Skill:]) **using 'AND' instead
of
'Or' here, which seemed intuitive to me, doesn't work**
AND (([Employee Query].EmployeeID)=([Employee-Skills].[EmployeeID])));
AND ((Employee-Skills.SkillID)=([Skills Query].[SkillID])) **joins all
my
tables together**

I looked through some old responses and found mention of 'InStr' but
can't
quite figure out how to use it appropriately.

Can someone please help?