Find Missing Value

[Tom]

I have a one-to-many relationship table (a Board has many members).

Member positions can be a) Chairman, b) Secretary, or c) Member.

I need to create a query that determines if there is an existing board
which:
- does NOT have designated Chairman
- does NOT have designated Secretary
- does NOT have designated Chairman or Secretary

Certainly, if a board has members of all levels I don't want to pick up
those records.


Does anyone know how to create such query?


Tom

 

[Allen Browne]

Use a subquery to select the record from the Board table thave no matching
record in the Member table where the MemberPostion is other than just
'Member'.

Something like this:

SELECT * FROM Board WHERE NOT EXISTS
( SELECT MemberID FROM Member
WHERE Member.BoardID = Board.BoardID
AND MemberPosition <> 'Member' );

 

[Michel Walsh]

Hi,


Many possible solutions. One "intuitive" one is to GROUP BY board,
but...wait, can HAVING 0=COUNT(*) works? surely not, if the COUNT(*), the
number of records, in a group, is zero, the group won't exist. So, does that
means we cannot do with a simple GROUP BY query? no, we can.

SELECT board
FROM somewhere
GROUP BY board
HAVING 0 = MIN(Position='secretary')
OR 0 = MIN(Position='chairman')



In other words, for all records in a given group, Position='secretary' will
be evaluated. With Jet, that returns a 0 (false) or a -1 (true). The
minimum of all evaluations returns -1? that means we have a record where
position='secretary', for that group, else, the minimum of all evaluations
return 0? that means we have no record at all, for this group, where
position='secretary'... we want THOSE groups, so

HAVING 0=MIN(Position='secretary')



returns the groups having no secretary. In other words, I use the MIN
aggregate, MIN{a, b, c, ...} as if it was a OR aggregate (which does not
exist, textually), OR{x, y, z, ... }.



If you want the boards having AND no secretary, AND no chairman:



SELECT boards.boardName
FROM boards INNER JOIN members
ON boards.boardID = members.boardID
GROUP BY boards.boardName
HAVING 0 = MIN(members.Position='secretary')
AND 0 = MIN(members.Position='chairman')


Note that you probably do not need the inner join, since you can probably do
the job with:



SELECT boardID
FROM members
GROUP BY boardID
HAVING 0 = MIN(Position='secretary')
AND 0 = MIN(Position='chairman')




Sure, you can also go for a NOT EXISTS ( or some LEFT JOIN ), but I doubt
the syntax can be "as nice as", at first look...



Hoping it may help,
Vanderghast, Access MVP

 

[Tom]

Allen:

Thanks, that worked great!

Tom

Use a subquery to select the record from the Board table thave no matching
record in the Member table where the MemberPostion is other than just
'Member'.

Something like this:

SELECT * FROM Board WHERE NOT EXISTS
( SELECT MemberID FROM Member
WHERE Member.BoardID = Board.BoardID
AND MemberPosition <> 'Member' );

 

[Tom]

Mike:

Awesome... I tried Allen's and your suggestions... they all work out well.

Tom