How to reference a field in one table twice from within another

[Guest]

How do I create multiple joins from within one table to one field in another
table? Example: a recruitment database contains information about a
potential hire. Within each record are fields containing Recruiter
information (screened by, interviewed by, hired by). Each of those fields
contains a unique identifier which I want to link back to the Recruiter table.

 

[Allen Browne]

Add the table to the Relationships window (Tools | Relationships) twice.

Access aliases the 2nd copy with a suffix, e.g. Recruiter_1. You can then
create the relationships between the 2 copies of the same table.

The technique is known as a self-join, and there are lots of applications
where it is useful. The querying starts to get interesting when you trace
this back through several generations. For example, an Employee table with a
SupervisorID field that links back to another person in the Employee table,
and you start asking questions like, "Who is my boss's boss?" or "List all
employees supervised by Jo Smith, directly or indirectly."

If you want to research this further, there's an example going back 4
generations of self-join here:
Self Joins: tables that look themselves up (Pedigrees example)
at:
http://allenbrowne.com/ser-06.html
and an example of assemblies that contain subassemblies here:
Bill Of Materials
at:
http://www.mvps.org/access/modules/mdl0027.htm

 

[Guest]

Thank you so much! Works like a charm.

Although I've used the discussion group alot, this is the first time I've
posted a question. I'm VERY impressed with the speed that my post was
answered, and with the quality of the response.

Thanks Again!!