query with variable number of criteria

  • Thread starter Thread starter Brendan
  • Start date Start date
B

Brendan

Hi,
simplified, 3 tables

table 1 table 2 table 3
joe ford
joe=ford
sam chevy
joe=chevy
harry toyota
joe=toyota

sam=ford

harry=chevy

harry=ford

I need a query which will tell me, from a form, for example

who has at least a ford and a chevy?
who has at least a toyota
who has at least a ford, chevy AND toyota?


Thank you for any advice.
BJC
 
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
 
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
 
Hi,


Indeed, the COUNT technique implies there is no duplicated tuple, here
(ownerID, CarType), neither in the second table (if a second table is used
to described the wanted CarType) should there be duplicated CarType values.


Vanderghast, Access MVP


John Spencer (MVP) said:
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
Click to expand...
Click to expand...
 
Well, I hope the OP has a solution to use.

I frequently learn from your postings and would like to say thanks for sharing
your knowledge.

Michel said:
Hi,

Indeed, the COUNT technique implies there is no duplicated tuple, here
(ownerID, CarType), neither in the second table (if a second table is used
to described the wanted CarType) should there be duplicated CarType values.
Click to expand...
SNIP
 
Back
Top