confused query

[lynn atkinson]

I have a database which records training awards, completed or in
progress(status). I would like to find those people who have completed awards
'management 3' AND 'care 3'. How do I represent this in a query. I have a
progress table which documents the award and the status so the completed
management 3 and completed care 3 records are separate records - is this the
problem.
the sql I have at present is
(((progress.status)="completed") AND ((progress.award)="management 3" And
(progress.award)="care 3");

But this does not come up with the results I need. What am I missing?
Hope this makes sense. Can anyone help?

 

[Ofer]

You can try this query, filter on the Status and Award, and then group by and
return all the people with two entries

SELECT Count(progress.CustName) AS CountCustName, progress.CustName
FROM progress
WHERE (((progress.award) In ("management 3","care 3")) AND
((progress.status)="completed"))
GROUP BY progress.CustName
HAVING (((progress.CustName))>1))

change the cust name to your field name

 

[Van T. Dinh]

You need to use either the EXISTS clause or In Clause with SubQueries to
pick up the person with 2 awards.

an example using In Clause:

****Untested****
SELECT Main.PersonID
FROM [progress] AS Main
WHERE Main.PersonID IN
( SELECT Sub1.PersonID
FROM [progress] AS Sub1
WHERE (Sub1.award = "management 3")
AND (Sub1.progress = "completed") )
AND Main.PersonID IN
( SELECT Sub2.PersonID
FROM [progress] AS Sub2
WHERE (Sub2.award = "care 3")
AND (Sub2.progress = "completed") )
********