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.

 

[KARL DEWEY]

The 'One' side of the relationship must have a primary key field designated.
In most applications an autonumber works well and the related table then has
a number - integer as a foreign key field.