Prev: concatenating two vendor codes
Next: Query SELECT in SELECT?
From: Toppo on 21 Jan 2010 06:07
I have a database (Access 2007) that is used for holding data on and
communicating with FE colleges. Obviously (?) each college has a number of
contacts (up to 14) and these are represented in two tables – one for College
Details and one for Contact Details which are linked by a one to many
relationship. Most colleges have one representative on one of 3 groups, but
not all colleges are represented on all groups and some not on any. The
membership of a group is noted by a Yes/No field in the Contact details.
Getting a query to run to identify membership of groups is OK. But I can't
figure out how to get a query result with a row per COLLEGE that has no
representation on either one or more of the groups. What I get is a
duplication of the college name (as they identify the number of contacts who
are not members of a group). The only way I can figure it is to have a field
in the College Table which mirrors the one in the Contacts Table, but I don't
want to enter the same data twice & cannot link the tables, so by completing
one Yes/No field it automatically completes the Yes/No field in the other
table.
From: Daryl S on 21 Jan 2010 10:00
Toppo -

You want a query that shows colleges with no memberships.
You can use the 'not exists' with a subquery to find these.
Try this, substituting your table and field names for mine.

Select CollegeDetails.CollegeName from CollegeDetails
Where not exists (Select 'x' from ContactDetails
where ContactDetails.CollegeID = CollegeDetails.CollegeID
AND ContactDetails.GroupMember = True)
--
Daryl S


"Toppo" wrote:

> I have a database (Access 2007) that is used for holding data on and
> communicating with FE colleges. Obviously (?) each college has a number of
> contacts (up to 14) and these are represented in two tables – one for College
> Details and one for Contact Details which are linked by a one to many
> relationship. Most colleges have one representative on one of 3 groups, but
> not all colleges are represented on all groups and some not on any. The
> membership of a group is noted by a Yes/No field in the Contact details.
> Getting a query to run to identify membership of groups is OK. But I can't
> figure out how to get a query result with a row per COLLEGE that has no
> representation on either one or more of the groups. What I get is a
> duplication of the college name (as they identify the number of contacts who
> are not members of a group). The only way I can figure it is to have a field
> in the College Table which mirrors the one in the Contacts Table, but I don't
> want to enter the same data twice & cannot link the tables, so by completing
> one Yes/No field it automatically completes the Yes/No field in the other
> table.
 | 
Pages: 1
Prev: concatenating two vendor codes
Next: Query SELECT in SELECT?