Multiple Value Query AND values

[Jon Lewis]

Hi All

I've a tables of Accounts, Categories and a table (to manage the
many-to-many relationship) AccountsCategories.

If I need a query which returns all Accounts of Category i AND j AND k (as
opposed to OR) the following doesn't work (as it does with OR):

SELECT DISTINCT tblAccountsCategories.AccountID
FROM tblAccountsCategories
WHERE tblAccountsCategories.CategoryID=i AND
tblAccountsCategories.CategoryID=j AND AND
tblAccountsCategories.CategoryID=k

presumably because for each record the CategoryID can only have one value.
The only way I've got this sort of query to work is something like the
following which is looping through a multiselectable ListBox of Categories :

Dim lCount As Long
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("Select Distinct [AccountID] FROM
tblAccountsCategories")
With rs
If Not .EOF Then
For lCount = 0 To Me.lstSelectedCategory.ListCount - 1
If .EOF Then
Exit For
End If
.Filter = "[AccountID] IN (SELECT [AccountID] FROM
tblAccountsCategories WHERE [CategoryID]= '" &
Me.lstSelectedCategory.ItemData(lCount) & "')"
Set rs = .OpenRecordset
Next
End If
End With


Is there an easier/better way?

TIA

 

[Allen Browne]

You're right: no record can possibly match the criterion that the CategoryID
is i and also j and also k.

(Also the text values need to be in quotes.)

If we can assume you have a unique index on AccountID + CategoryID (so you
can't have 2 records that say the account is in the same category), you
could do it like this:

SELECT tblAccountsCategories.AccountID
FROM tblAccountsCategories
WHERE tblAccountsCategories.CategoryID IN ("i", "j", "k")
HAVING Count(tblAccountsCategoriesAccountID) = 3;

 

[Jon Lewis]

Yep - there is a unique index on the junction table. So its the same as for
OR'ed multivalue query, not DISTINCT, of COUNT = number of categories OR'ed
so the Account must belong to all Categories. Cool - and much easier than
my method.

Thanks Allen

You're right: no record can possibly match the criterion that the
CategoryID is i and also j and also k.

(Also the text values need to be in quotes.)

If we can assume you have a unique index on AccountID + CategoryID (so you
can't have 2 records that say the account is in the same category), you
could do it like this:

SELECT tblAccountsCategories.AccountID
FROM tblAccountsCategories
WHERE tblAccountsCategories.CategoryID IN ("i", "j", "k")
HAVING Count(tblAccountsCategoriesAccountID) = 3;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi All

I've a tables of Accounts, Categories and a table (to manage the
many-to-many relationship) AccountsCategories.

If I need a query which returns all Accounts of Category i AND j AND k
(as opposed to OR) the following doesn't work (as it does with OR):

SELECT DISTINCT tblAccountsCategories.AccountID
FROM tblAccountsCategories
WHERE tblAccountsCategories.CategoryID=i AND
tblAccountsCategories.CategoryID=j AND AND
tblAccountsCategories.CategoryID=k

presumably because for each record the CategoryID can only have one
value. The only way I've got this sort of query to work is something like
the following which is looping through a multiselectable ListBox of
Categories :

Dim lCount As Long
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("Select Distinct [AccountID] FROM
tblAccountsCategories")
With rs
If Not .EOF Then
For lCount = 0 To Me.lstSelectedCategory.ListCount - 1
If .EOF Then
Exit For
End If
.Filter = "[AccountID] IN (SELECT [AccountID] FROM
tblAccountsCategories WHERE [CategoryID]= '" &
Me.lstSelectedCategory.ItemData(lCount) & "')"
Set rs = .OpenRecordset
Next
End If
End With

 

[Jon Lewis]

For future reference Allen's solution does appear to need a Group By clause
to work:

SELECT tblAccountsCategories.AccountID
FROM tblAccountsCategories
WHERE tblAccountsCategories.CategoryID IN ("i", "j", "k")
GROUP BY tblAccountsCategories.AccountID HAVING
Count(tblAccountsCategoriesAccountID) = 3;

Jon

You're right: no record can possibly match the criterion that the
CategoryID is i and also j and also k.

(Also the text values need to be in quotes.)

If we can assume you have a unique index on AccountID + CategoryID (so you
can't have 2 records that say the account is in the same category), you
could do it like this:

SELECT tblAccountsCategories.AccountID
FROM tblAccountsCategories
WHERE tblAccountsCategories.CategoryID IN ("i", "j", "k")
HAVING Count(tblAccountsCategoriesAccountID) = 3;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi All

I've a tables of Accounts, Categories and a table (to manage the
many-to-many relationship) AccountsCategories.

If I need a query which returns all Accounts of Category i AND j AND k
(as opposed to OR) the following doesn't work (as it does with OR):

SELECT DISTINCT tblAccountsCategories.AccountID
FROM tblAccountsCategories
WHERE tblAccountsCategories.CategoryID=i AND
tblAccountsCategories.CategoryID=j AND AND
tblAccountsCategories.CategoryID=k

presumably because for each record the CategoryID can only have one
value. The only way I've got this sort of query to work is something like
the following which is looping through a multiselectable ListBox of
Categories :

Dim lCount As Long
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("Select Distinct [AccountID] FROM
tblAccountsCategories")
With rs
If Not .EOF Then
For lCount = 0 To Me.lstSelectedCategory.ListCount - 1
If .EOF Then
Exit For
End If
.Filter = "[AccountID] IN (SELECT [AccountID] FROM
tblAccountsCategories WHERE [CategoryID]= '" &
Me.lstSelectedCategory.ItemData(lCount) & "')"
Set rs = .OpenRecordset
Next
End If
End With

 

[Allen Browne]

Yes: it does. Thanks for posting that.