Problem using ALL/ANY in a subquery

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Access 2003 front-end (running in SQL Compatible Mode) Linked to
tables in SQL Server 2000.

I have a table (tblVolunteer_Ailment) that links volunteers (tblVolunteer)
to ailments (tlkpAilment).

iVolunteerID iAilmentID
1 1
2 1
2 2
3 2

I am trying to write 2 queries:
The first (which works) returns a list of volunteerIDs which have any of the
ailments specified:

SELECT DISTINCT tblVolunteer_Ailment.iVolunteerID
FROM tblVolunteer_Ailment
WHERE (((tblVolunteer_Ailment.iAilmentID)=Any (SELECT iAilmentID FROM
tlkpAilment WHERE iAilmentID IN (1,2))));

This returns 1,2,3

The second query should only return a list of volunteers that have all the
ailments specified.

SELECT DISTINCT tblVolunteer_Ailment.iVolunteerID
FROM tblVolunteer_Ailment
WHERE (((tblVolunteer_Ailment.iAilmentID)=All(SELECT iAilmentID FROM
tlkpAilment WHERE iAilmentID IN (1,2))));

However this returns no records. I am expecting it to return 1 record for
iVolunteerID 2

I eventually intend to build the queries programmatically, and I am using IN
because there may be numerous ailments that the user selects from a list.

Any help appreciated.
 
Dear Pete:

There's a bit of a trick to doing this. It goes like this.

If you count all the Ailments (you have 2 showing, but we should query
tlkpAilment to see how many there are) and if you count the DISTINCT
ailments of each Volunteer, then when they are equal, that volunteer has all
the possible ailments.

Make sense?

Now, the total number of possible ailments might be:

SELECT COUNT(*) AilmentCount
FROM tlkpAilment

Does this give the value desired?

The number of different ailments for each volunteer would be:

SELECT iVolunteerID, COUNT(*) AilmentCount
FROM (
SELECT DISTINCT iVolunteerID, iAilmentID
FROM tblVolunteer_Ailment
) x
GROUP BY iVolunteerID

Does this give the number of distinct ailments for each volunteer correctly?

If we're in good shape so far, then:

SELECT iVolunteerID
FROM (
SELECT DISTINCT iVolunteerID, iAilmentID
FROM tblVolunteer_Ailment
) x
GROUP BY iVolunteerID
HAVING COUNT(*) = (
SELECT COUNT(*)
FROM tlkpAilment)

Did this do it?

Do you see how the component parts go together? Good! Now you can do
things like this for yourself! Give a man a fishing pole!

Tom Ellison
 
Hi Tom

Thanks for your reply. I have got it working!

The idea of counting the items had occured to me, although had seemed a bit
of a convoluted way of doing it! I thought I would try and find out why ALL
wasn't working - when you read the Access help it looks like it should and it
would be a 'neater' solution. Maybe it is the '=' ALL that is the cause of
the problem?

Anyway, many thanks for your help.

Regards
--
Peter Schmidt
Ross-on-Wye, UK


Tom Ellison said:
Dear Pete:

There's a bit of a trick to doing this. It goes like this.

If you count all the Ailments (you have 2 showing, but we should query
tlkpAilment to see how many there are) and if you count the DISTINCT
ailments of each Volunteer, then when they are equal, that volunteer has all
the possible ailments.

Make sense?

Now, the total number of possible ailments might be:

SELECT COUNT(*) AilmentCount
FROM tlkpAilment

Does this give the value desired?

The number of different ailments for each volunteer would be:

SELECT iVolunteerID, COUNT(*) AilmentCount
FROM (
SELECT DISTINCT iVolunteerID, iAilmentID
FROM tblVolunteer_Ailment
) x
GROUP BY iVolunteerID

Does this give the number of distinct ailments for each volunteer correctly?

If we're in good shape so far, then:

SELECT iVolunteerID
FROM (
SELECT DISTINCT iVolunteerID, iAilmentID
FROM tblVolunteer_Ailment
) x
GROUP BY iVolunteerID
HAVING COUNT(*) = (
SELECT COUNT(*)
FROM tlkpAilment)

Did this do it?

Do you see how the component parts go together? Good! Now you can do
things like this for yourself! Give a man a fishing pole!

Tom Ellison
Click to expand...
 
Back
Top