"AND" category search

[radan]

I am trying to create a search folder which finds all e-mails with two types
of categories assigned to them. like

Cat1 AND Cat2

When I select the categories, the sintax is
Cat1, Cat2

and that does an OR search

Can anyone help with this?

 

[John Spencer]

Where are the categories stored? Do you have a table that stories
multiple categories for one email record or do you have multiple
category fields?

Assumptions:
== Table that contains emailID and Category (0ne record for each
combination of emailId and Category and no duplicates on the category)

SELECT *
FROM Emails
WHERE Emails.EmailID in (
SELECT EmailID
FROM EmailCategories
WHERE EmailCategories.CategoryID in ("Cat1","Cat2")
GROUP BY EmailID
HAVING Count = 2)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[radan]

sorry guys, wrong thread category

Where are the categories stored? Do you have a table that stories
multiple categories for one email record or do you have multiple
category fields?

Assumptions:
== Table that contains emailID and Category (0ne record for each
combination of emailId and Category and no duplicates on the category)

SELECT *
FROM Emails
WHERE Emails.EmailID in (
SELECT EmailID
FROM EmailCategories
WHERE EmailCategories.CategoryID in ("Cat1","Cat2")
GROUP BY EmailID
HAVING Count = 2)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================