update a field by inputting data in a different table

[Toppo]

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.

 

[Daryl S]

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)