How do I link one person to another in the same table?

[E289]

I have a table of Contacts. Some of my contacts know other contacts, and I
would like to indicate that in their record. However, for Data Type, I
cannot create a drop down list of contacts from within the table to choose
from.

Any idea how to to this? Or, if this is not possible, can you suggest
another way to indicate "who knows whom"?

 

[Steve]

Your contact table ought to look like:
TblContact
ContactID
FirstName
LastName
etc

Then you need am acquaintenances table:
TblAcquaintenance
AcquaintenanceID
ContactID (identifies a Contact)
AcquaintedContactID (Identifies a contact ContactID knows)

If ContactID 21 knows ContactIDs 1,7, 32 and 47, TblAcquaintenance would
lool like:
1 21 1
2 21 7
3 21 32
4 21 47

Steve
(e-mail address removed)

 

[Gina Whipp]

E289,

Try creating using a Query based on that table and use the Query as the
RowSource in the Combo Box on your form.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[KARL DEWEY]

Use an Autonumber field as primary key field in the Contacts table.

If the known contacts are only one way like employes to supervisor add a
number - long integer field also as Supervisor. Then in the relations
window add the Contacts table twice (Access will add a sufix, Contacts_1 to
the second instance). Create a one-to-many from first table primary key
field to the Supervisor field of the second (many employes can have the same
boss).

If the contacts are multiple in both directions then you need a junction
table, JCTContact with two number - long integer fields. Name them something
like Cont1 and Cont2. The in the relations window add the Contacts table
twice and the junction table once. Create a one-to-many from first table
primary key field to the Cont1 field of the junction table. Repeat from
second Contacts to Cont2.

Use form/subform for Contact to Contact. The subform to have combo to
select associated contact for person shown in the main. Use scrolling to
locate person in main.