Remove the referring physician link to the doctors table. Add
the doctors table a second time. Then link the referring
physician foreign key to the the second doctors table. The
underlying query processor will actually treat this query as
if there were three separate table involved. Doctors_1 is
technically an alias for the doctors table.
Results
We now get each admissions record with the admitting and
referring physicians.
Referential Integrity
This solution to using roles in a Microsoft
Access database will preserve referential integrity if set.
It will not be possible to enter an admissions record that
does not refer to a valid entry in the doctors table. Nor
will you be able to delete doctors that have patients
assigned either as a referring or admitting physician unless
you cascade deletes.
Concluding Thoughts
You can use the technique in this article if you have
entities (tables) that play multiple roles and you wish to
enforce referential integrity. If you do not care to enforce
referential integrity then you probably won't need to create
a relationship design. But you will still need to add the
table multiple times in the query designer as I have shown.
Dan D'Urso
Laguna Niguel, CA 92677
July 2012
articles at dhdurso dot org
