Link records in the same table

K

Khoy

Hi there,

I seem to have non-conventional question and would be greatful if someone
can point me in the right direction. Instead of linking records between
different tables I would like to create links between records in the same
table.

I have a very simple database (still) only consisting of a single form and
table and and want to create links between record in the same table which are
related and ideally navigety back and forth between all linked records - Much
like the Thesaurus function in MS Word.
Eventually the dream scenario would be to have listbox on my form which
allows multiple entries of all the other records in the table related to the
displayed record and obviously also allow me to change the list.

Do I have to do this with a subform and a link table or is there and easier
way of doing it?

Any help would be appreciated.
Regards
 
R

Rick B

Telling us something about the types of records and the structure of your
table would help.

In most cases you could accomplish this by adding a second table and
creating a "parent" id of some kind then using that in your main table. For
example, if you have five records in your table that are all shades of blue,
you could add a field and store "blue" in it. If you have five other
records that are shades of green, you'd store "green" in their record. The
second table I mentioned would list all the possible entries.

Unless you tell us more about the structure, it would be hard to help you
here.
 
J

John Vinson

Hi there,

I seem to have non-conventional question and would be greatful if someone
can point me in the right direction. Instead of linking records between
different tables I would like to create links between records in the same
table.

I have a very simple database (still) only consisting of a single form and
table and and want to create links between record in the same table which are
related and ideally navigety back and forth between all linked records - Much
like the Thesaurus function in MS Word.
Eventually the dream scenario would be to have listbox on my form which
allows multiple entries of all the other records in the table related to the
displayed record and obviously also allow me to change the list.

Do I have to do this with a subform and a link table or is there and easier
way of doing it?
Click to expand...

It seems you're talking about one of my favorite obscure query types,
the Self Join.

There are a couple of flavors of this: a "direct self join" is
appropriate for heirarchical data, where each record may (or may not)
have one or more child records. This is easily handled by adding a
ParentID field to the table, which will serve as a foreign key to the
Primary Key value of the parent record. You can do data entry and
display by simply having a Form with a Subform, using the primary key
as the Master Link Field and the ParentID as the Child Link Field.

Or you may have a many to many link in which each record in the table
can relate to multiple other records; a classic example is a geneology
database where one person may be Husband to another person, Father to
a second, Brother to a third, and so on. For this you need a
Relationships table with two foreign keys, each linked to the Primary
Key of the main table, and (optionally) a relationship type. Again,
base a Subform on this relationship table to see all the related
records.

John W. Vinson[MVP]
 
Top