Cleaner way to query for negatives?

[Allison]

Access 2000, Win XP SP2

Hi all,

I'm looking for a cleaner way to determine whether or not an entry in one
table has a specific field checked yes or no in another table.

Table 1:
fldVendorID (Primary key, no dupes)
fldVendorName

Table 2:
fldVendorID (dupes allowed)
fldContact
fldAllowed

Table 2 can have many fldContacts per each fldVendorID from Table 1.
fldAllowed is a yes/no field.

I want to query for any VendorID which has NO fldAllowed=yes.

I've done it this way:

Query 1 counts all contacts from Table 2, grouped by fldVendorID

Query 2 counts all fldAllowed="No", grouped by fldVendorID

Query 3 joins both fields of Queries 1 and 2, and returns results only if
both field counts from the two queries match each other.

This works. But, can it be done cleaner? If so, how?

 

[Douglas J. Steele]

SELECT fldVendorID, fldVendorName
FROM Table1
WHERE fldVendorID IN
(SELECT DISTINCT fldVendorID FROM Table2 WHERE fldAllowed = False)

 

[Marshall Barton]

Access 2000, Win XP SP2
I'm looking for a cleaner way to determine whether or not an entry in one
table has a specific field checked yes or no in another table.

Table 1:
fldVendorID (Primary key, no dupes)
fldVendorName

Table 2:
fldVendorID (dupes allowed)
fldContact
fldAllowed

Table 2 can have many fldContacts per each fldVendorID from Table 1.
fldAllowed is a yes/no field.

I want to query for any VendorID which has NO fldAllowed=yes.

I've done it this way:

Query 1 counts all contacts from Table 2, grouped by fldVendorID

Query 2 counts all fldAllowed="No", grouped by fldVendorID

Query 3 joins both fields of Queries 1 and 2, and returns results only if
both field counts from the two queries match each other.

This works. But, can it be done cleaner? If so, how?

Maybe this is "Cleaner"?

SELECT fldVendorID
FROM Table2
GROUP BY fldVendorID
HAVING Count(*) = -Sum(fldAllowed)

or. if that HAVING clause is too cryptic:
HAVING Count(*) = Sum(IIffldAllowed, 1, 0))

 

[John Spencer]

I would try something like the following. Assumption is that your table
names consist of only letters, numbers, and underscore characters.

SELECT V.fldVendorID, V.fldVendorName
FROM Table1 as V LEFT JOIN
(SELECT fldVendorID
FROM Table2
WHERE fldAllowed = True) as T
ON V.fldVendorID = T.fldVendorID
WHERE T.fldVendorID is null



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================