Linking fields in different table in same database

[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 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 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 and by completing one it
automatically completes the ‘mirror’ field in the other table. I don’t want
to enter the same data twice but cannot link the tables.

 

[Douglas J. Steele]

Open your query in Design View.

There will be a line joining the two tables. Highlight that line, then
right-click and select Join Properties. One of the three choices will be
"Include ALL records from 'College Details' and only those records from
'Contact Details' where the joined fields are equal." Select that and try
running your query again.

 

[John Spencer]

Assumptions:
== field in the contacts table that identifies a group that the contact belongs to
== Field in the contacts table that identifies the college

To identify colleges that have no contact in a specific group
SELECT Colleges.*
FROM Colleges
WHERE NOT EXISTS
(SELECT * FROM Contacts
WHERE Group = "GroupName" AND Contacts.CollegeID = Colleges.CollegeID)

To identify colleges that have no contact in any of the groups
SELECT Colleges.*
FROM Colleges
WHERE NOT EXISTS
(SELECT * FROM Contacts
WHERE Group Is Null AND Contacts.CollegeID = Colleges.CollegeID)

To check all groups
SELECT Colleges.*
, EXISTS
(SELECT * FROM Contacts
WHERE Group = "Registrar"
AND Contacts.CollegeID = Colleges.CollegeID) As RegistrarContact
, EXISTS
(SELECT * FROM Contacts
WHERE Group = "Faculty"
AND Contacts.CollegeID = Colleges.CollegeID) As FacultyContact
, EXISTS
(SELECT * FROM Contacts
WHERE Group = "Sports Director"
AND Contacts.CollegeID = Colleges.CollegeID) As SportsDirectorContact
FROM Colleges


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County