One problem with that solution. I have three fords. NOW, with the last query,
I will be returned since I have 3 cars that meet the criteria, even though none
of the three are a toyota or a chevy.
In other words, depending on the rules (am I allowed to own two vehicles from
the same manufacturer), the proposed solution could work or could fail.
Right now, I don't see any simple solution --- unless you build a query/subquery
of unique records and then run the proposed solution against that. With Access
the query might look something like the following. Note that the subquery
starts and end with square brackets, has a period after the closing bracket, AND
does not contain any square brackets within the subquery itself.
SELECT A.OwnerID
FROM
[SELECT Distinct OwnerId, CarType
FROM Table]. as A
WHERE A.CarType IN('ford', 'chevy')
GROUP BY A.OwnerID
HAVING COUNT(A.OwnerID)=2
Michel said:
HI,
Your data is not readable (probably due to the use of a proportional font,
and excessive width).
If you have one table, like:
OwnerID, CarType 'fields
joe ford
sam chevy
joe chevy
harry toyota
...
Who has (at least) and a ford and a chevy:
SELECT OwnerID
FROM myTable
WHERE CarType IN('ford', 'chevy')
GROUP BY OwnerID
HAVING COUNT(*)=2
Who has a (at least) a toyota (to keep the same style)
SELECT OwnerID
FROM myTable
WHERE CarType IN('toyota')
GROUP BY OwnerID
HAVING COUNT(*)=1
ford, chevy and toyota:
SELECT OwnerID
FROM myTable
WHERE CarType IN('ford', 'chevy', 'toyota')
GROUP BY OwnerID
HAVING COUNT(*)=3
If the choices (ford, chevy, toyota, ) are in a table, wanted, under a
field, want,
then:
SELECT OwnerID
FROM myTable INNER JOIN wanted
ON myTable.CarType=wanted.want
GROUP BY OwnerID
HAVING COUNT(*)= (SELECT COUNT(*) FROM wanted)
Hoping it may help,
Vanderghast, Access MVP
