Doing an 'AND' query on the same field

[Bob]

I need to do an AND query on the same field in a table. For example, in the
simple table that links documents and their associated keywords I might have

doc1 key1
doc1 key2
doc1 key3
doc2 key2
doc2 key3
doc2 key4
doc3 key3
doc3 key1

If I queried on keys key2 AND key3 I want both doc1 and doc2 returned.
Simple? It ought to be but I can not figure out how to do it. A simple
SELECT WHERE key=key2 AND key=key3 will not work for obvious reasons: a key
can not have more than one value.

Do I have to use a subquery? If so how would I code it?

Bob

 

[Martin]

Try OR instead of AND.

I get the usage of OR vs AND mixed up until I see the results the first
time, too.

 

[Bob]

The OR option works as expected: it results in either one value or the other.
But I want to use AND in order to return a result that has both values.

Thanks,
Bob

 

[Ofer]

With AND the criteria you defining looks in each record that both conditions
are correct
For example A1=1 And A1=2
It will look for a condition where A1 = (1 and 2) in the same record, so it
will never find a match, because A1 can be equal to 1 or 2, but not to both.

If you want either record where A=1 Or A=2, you use Or

You will use AND where you want to find a filter that combine to seperate
field
Where A1=1 And A2=2

 

[Martin]

I think I understand now. Using your sample table and query, you are
expecting the results to be "doc1" and "doc2" because both of these have both
"key2" and "key3".

I have not used this, but I have seen this in other posts. Try the IN
clause on the WHERE statement like:
WHERE key IN("key2",'key3")

Hopefully, this helps.

 

[Michel Walsh]

Hi,



Assuming you have no duplicated record:


SELECT doc
FROM myTable
WHERE key IN( param1, param2 )
GROUP BY doc
HAVING COUNT(*)=2




the magic number 2 comes from the fact that you supplied 2 values.


I assumed the field name were doc and key.


Hoping it may help,
Vanderghast, Access MVP

 

[Amy Blankenship]

Try joining the table to itself (show the table twice, then drag the
documents column in one table to the documents table in the copy)

Then show the documents value from just one table and the keywords from both
tables. In the criteria put "key2" in the criteria, then "key3" in the OR
for the same table. Repeat for the copy.

This should give you what you want. (Hopefully...I'm doing it off the top
of my head).

HTH;

Amy