Novice Question: Problems with using "And" on the same field

  • Thread starter Thread starter Lynne
  • Start date Start date
L

Lynne

I'm a big novice to SQL and access so I appologize ahead of time:

But, I have a database that includes student records, current jobs
that are open, companies offering those jobs, recruiters for those
companies, and skills those jobs require.

I was trying to write a query that would list only jobs that required
BOTH communication and leadership skills.

Tried using "AND" and that simply returned nothing.. because no single
record had both Leadership and Communication in it's "row". Using
"OR" I get, of course, duplicate records that have both the skills, as
well as single records that have only one of the skills.

I thought maybe I could somehow do a count and return only records
that appeared >1 times... didn't work.

Any help?

Thank you

Abigael

---
Current Access SQL (generated VIA design view):

SELECT Job.JobID, Job.JobTitle, Job.JobStatus, Job.JobDescription,
Job.RID, Job.JobLocation, Job.JobType, Skill.SkillName
FROM Skill INNER JOIN (Job INNER JOIN JobSkills ON Job.JobID =
JobSkills.JobID) ON Skill.SkID = JobSkills.SkID
GROUP BY Job.JobID, Job.JobTitle, Job.JobStatus, Job.JobDescription,
Job.RID, Job.JobLocation, Job.JobType, Skill.SkillName
HAVING (((Skill.SkillName)="Leadership" Or
(Skill.SkillName)="Communication"));
 
Lynne said:
I'm a big novice to SQL and access so I appologize ahead of time:

But, I have a database that includes student records, current jobs
that are open, companies offering those jobs, recruiters for those
companies, and skills those jobs require.

I was trying to write a query that would list only jobs that required
BOTH communication and leadership skills.

Tried using "AND" and that simply returned nothing.. because no single
record had both Leadership and Communication in it's "row". Using
"OR" I get, of course, duplicate records that have both the skills, as
well as single records that have only one of the skills.

I thought maybe I could somehow do a count and return only records
that appeared >1 times... didn't work.

Any help?

Thank you

Abigael

---
Current Access SQL (generated VIA design view):

SELECT Job.JobID, Job.JobTitle, Job.JobStatus, Job.JobDescription,
Job.RID, Job.JobLocation, Job.JobType, Skill.SkillName
FROM Skill INNER JOIN (Job INNER JOIN JobSkills ON Job.JobID =
JobSkills.JobID) ON Skill.SkID = JobSkills.SkID
GROUP BY Job.JobID, Job.JobTitle, Job.JobStatus, Job.JobDescription,
Job.RID, Job.JobLocation, Job.JobType, Skill.SkillName
HAVING (((Skill.SkillName)="Leadership" Or
(Skill.SkillName)="Communication"));
Click to expand...

Hi Abigael,

I believe one method would be to use the technique
where you "Sum identities."

Eliminate SkillName from your group
so you are only grouping by each job.
Then within each job group, you can get
a "negative count" of Leadership and
Communication by summing the identities

Sum([SkillName]="Leadership")

Sum([SkillName]="Communication")

Then you can filter for those sums
that are less than zero.

I believe your query would look something like:


SELECT Job.JobID, Job.JobTitle, Job.JobStatus, Job.JobDescription,
Job.RID, Job.JobLocation, Job.JobType,
"Leadership, Communication" As RequiredSkills
FROM Skill INNER JOIN (Job INNER JOIN JobSkills ON Job.JobID =
JobSkills.JobID) ON Skill.SkID = JobSkills.SkID
GROUP BY Job.JobID, Job.JobTitle, Job.JobStatus, Job.JobDescription,
Job.RID, Job.JobLocation, Job.JobType
HAVING (((Sum([SkillName]="Leadership"))<0)
AND ((Sum([SkillName]="Communication"))<0));

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
May not be the most efficient but try the 2 SQL Strings (not tested):

****Untested SQL1****
SELECT J.JobID, J.JobTitle, J.JobStatus, J.JobDescription,
J.RID, J.JobLocation, J.JobType,
"Leadership & Communication" AS RequiredSkills
FROM Job AS J
WHERE
(
( SELECT Count(JS.JobID)
FROM JobSkills AS JS
INNER JOIN Skills AS S
ON JS.SkID = S.SkID
WHERE (JS.JobID = J.JobID)
AND ( (S.SkillName = "Leadership")
OR (S.SkillName = "Communication") )
) >= 2
)
********


Note: The above will select the Job that has, says, "Leadership" as the
JobSkill TWICE without the "Communication" Skill

The following SQL String will avoid the above situation (but probably less
efficient):

****Untested SQL2****
SELECT J.JobID, J.JobTitle, J.JobStatus, J.JobDescription,
J.RID, J.JobLocation, J.JobType,
"Leadership & Communication" AS RequiredSkills
FROM Job AS J
WHERE
(
( EXISTS
( SELECT *
FROM JobSkills AS JS
INNER JOIN Skills AS S
ON JS.SkID = S.SkID
WHERE ( JS.JobID = J.JobID )
AND (S.SkillName = "Leadership")
)
)
AND
( EXISTS
( SELECT *
FROM JobSkills AS JS
INNER JOIN Skills AS S
ON JS.SkID = S.SkID
WHERE ( JS.JobID = J.JobID )
AND (S.SkillName = "Communication")
)
)
)
********
 
Hi Abigael,

Just to show a *simple* example
tblJobSkills:

ID Job SkillRqd
1 job0 Duplicity
2 job0 Greed
3 job1 Leadership
4 job1 Communication
5 job2 Leadership
6 job3 Communication
7 job3 Ethical


As a test, can you tell which
job may be connected to the
mutual fund industry?

The following query would return
only Job1.


SELECT tblJobRequest.Job,
"Leadership, Communication" AS Skills
FROM tblJobRequest
GROUP BY tblJobRequest.Job
HAVING (((Sum([SkillRqd]="Leadership"))<0)
AND ((Sum([SkillRqd]="Communication"))<0));
 
First of all, THANKS for publishing your SQL statement. I might tackle this by
using two subqueries or by using the two instances of the Skill Table in my
query and joining them in sequence (Job to Skill to Skill_1). UNTESTED SQL
statements follow

SELECT Job.*
FROM Job
WHERE Job.JobID IN
(SELECT JS.JobID
FROM JobSkills as JS
WHERE JS.SkillName = "LeaderShip")
AND Job.JobID IN
(SELECT JS.JobID
FROM JobSkills as JS
WHERE JS.SkillName = "Communication")

ALTERNATIVE:
SELECT Job.*, S1.SkillName, S2.SkillName
FROM (Job INNER JOIN JobSkills as S1
ON Job.JobID = S1.JobID)
INNER JOIN JobSkills as S2
ON S1.JobID = S2.JobID
WHERE S1.SkillName = "LeaderShip"
AND S2.SkillName = "Communication"
 
ID Job SkillRqd
1 job0 Duplicity
2 job0 Greed
3 job1 Leadership
4 job1 Communication
5 job2 Leadership
6 job3 Communication
7 job3 Ethical


As a test, can you tell which
job may be connected to the
mutual fund industry?
Click to expand...

<SNORK!!!!>

Glad I'd finished my morning coffee, Gary...

Good answer and good example!
 
Thank you so much. Your solution worked. I won't claim to understand
the logic of why it works (the summing, etc.)... :o) ... but thank you
again for your help!

Thanks to everyone else also! I really appreciate it.

Abigael
 
John Vinson said:
<SNORK!!!!>

Glad I'd finished my morning coffee, Gary...

Good answer and good example!

John W. Vinson[MVP]
Click to expand...

Thanks John,

One rarely gets to "hear" the feedback (groans)
on newsgroups that are so commonly elicited
by my real-life prattle. 8-)

Actually...I think John Spencer's "alternate"
query probably would have lent itself best to
Abigael's situation....I could see her down the road
easily introducing parameters in the WHERE
clause.

I imagine that's the solution I will parrot
the next time something like this comes up. 8-)

Gary
 
Back
Top