Create a relationship between a table and itself

[markdeck]

Hi,
Microsofot's help claims that it is possible to create a link between a
table and itself. The text follows below from MS help:

"You can also create a relationship between a table and itself. This is
useful in situations where you need to perform a Lookup within the same
table. In the Employees table, for example, you can define a relationship
between the EmployeeID and ReportsTo fields, so that the ReportsTo field can
display employee data from a matching EmployeeID."

I can find nowhere that explains how.

Here's what I'd like to do: I'd like to create a database of "members" of a
family history that lists all of the information about every member.
However, because these family members will have married someone from within
the "members" table, I'd like to do a look up within the members table and
have that info appear in the form as well. How can I do this?
Or is my suggested method off?

Full disclosure: I'm researching this for a student at my school who is
doing this as an Access project. As the teacher unfortunatlely I can't
help her. Anybody?

 

[KARL DEWEY]

Open the Relationship window by clicking on TOOLS - Relationships.
Select the table by clicking on the icon that has a yellow plus sign and
datasheet. Select the same table twice. It will be shown in the window with
the second one having a suffix of underscore one like "_1" to indicate it is
the second with the same name.
Drag from the primary key of the first to the related field in the second --
EmployeeID - ReportsTo.

 

[markdeck]

Thanks,
I was able to figure that out...but was unable to make the link work in the
form. How would I set up the form so that I can see 2 different "records"
from the same form. When I try to create a lookup table (when setting up
the table) a message box tells me I can't create a lookup table linking back
to the same table?
Any ideas?

 

[KARL DEWEY]

You need to use a main form and a subform - Parent and Child.

 

[OsmarJr]

Take a look here: http://allenbrowne.com/ser-06.html