Using an AND for the same record

[lynn atkinson]

I would like to find someone who has 2 qualifications eg Care2 AND care3. The
table is progresstbl and has fields including the following
candidateID, award (eg care1, care2,care3), status (eg completed, working
towards, withdrawn)

I want to find all the candidates who have 'completed' 'care2' AND who have
'completed' 'care3'.

I have used the query builder, but am not getting the correct results as the
progress of each candidate and award is a separate record.

ie candidate 1 has completed care1
candidate 1 has completed care 2
candidate 1 is working towards care3
candidate 2 has completed care1
candidate 2 is working towards care2 etc

Can any one help?

 

[John W. Vinson/MVP]

I would like to find someone who has 2 qualifications eg Care2 AND care3.
The
table is progresstbl and has fields including the following
candidateID, award (eg care1, care2,care3), status (eg completed, working
towards, withdrawn)

I want to find all the candidates who have 'completed' 'care2' AND who
have
'completed' 'care3'.

A Subquery will work here: create a query based on the Candidates table
(*not* the progresstbl); on the CandidateID field put a criterion

IN (SELECT CandidateID From Progresstbl WHERE award = "care2") AND IN
(SELECT CandidateID FROM Progresstbl WHERE award = "care3")

John W. Vinson/MVP

 

[lynn atkinson]

OK this makes the whole thing rather complicated. The info I gave you was a
very simplified version of what I need.
I am trying to report on qualified personnel. What constitutes a qualified
person depends on their job title. I need to produce a report which displays
the following:
Qualified personnel:
in order to be qualified the following criteria need to be fulfilled
Job title = support assistant needs care2 AND starndard registration; or
Advanced registration only
job title= support worker needs care3 AND standard registration; OR advanced
registration only
Job title=assistant project manager needs care3 AND standard registration
AND management3

the job title is a field and I want to list all candidates who are eg
support workers who are qualified
all candidates who are support assistants who are qualified etc etc.

can you help - I am getting lost...

 

[John W. Vinson/MVP]

OK this makes the whole thing rather complicated. The info I gave you was
a
very simplified version of what I need.

Well, I'm off at a meeting replying from my hotel room, and I don't really
have the time to construct the whole query for you. But the two Subqueries -
the SELECT CandidateID FROM... in parentheses - can be any queries you wish;
they should each just return a list of CandidateID's, and can each have
independent criteria.

What you might want to do is set up Queries which retrieve just the
candidateID's of each of the different sets that you wish to use; select
View... SQL to get into SQL view, and copy and paste the SQL into the
subqueries.

John W. Vinson/MVP