cross referencing records from same table

[Guest]

Is there anyway I can link records that are in the same table. For example, I
have a datbase that is used for recording events. Events that are similar are
sometimes linked together and I wondered if there was a way I could have a
field that links to another record from the same table.

Thanks

 

[Guest]

I have a suggestion that may help. I designed a db for a photographer and
some of clients may know other clients so I built a one-to-many relationship
between one client and others. The many side uses the same table as the
source of a query. I filter the query so that it to excludes the client that
is currently chosen (if you understand!). That way you can link many records
from the same table.

I hope that helps.

 

[Allen Browne]

I'm not completely clear about what you are doing, but it is possible for
one record to refer to another in the same table.

For example, if and event (like a sales fair) arises as the result of
another fair, and you want to track which event gave rise to which other
events, you would add a field to the table like this:
ParentEventID Number

Assuming you already have an EventID (AutoNumber, primary key), you would
then create the relationship like this:

1. Open the Relationship Window (Tools menu.)

2. Add the Events table to the window a 2nd time.
Access will alias the 2nd copy as Events_1.

3. Drag Events.EventID and drop onto Events_1.ParentEventID.
Check the box for Relational Integrity, and create the relation.

In a similar way, you can use 2 copies of the table in a query. In query
design, you can set the Alias property of the table to whatever you want.

This kind of thing is called a self-join. It is quite common for anything
where you trace generations or subassemblies.

Another example:
Self Joins: tables that look themselves up (Pedigrees example)
at:
http://allenbrowne.com/ser-06.html

 

[Guest]

Excellent,

Thank you,

Now on to my next question...is there anyway that once I have selected the
record that is linked that it automatically updates the respective record. So
for example:

Event 1 is linked to Event 2 and I specify this by inserting it in to field
named 'Link' on the Event 1 record. Could I make the 'Link' field on Event 2
automatically link back to Event 1.

Heres the chain of what I would like to happen:

In the record, Event 1, I insert Event 2 (from a dropdown box, I have
already made).

After this field had been updated I would like Event 1 to be inserted in to
the 'Link' field on Event 2.

I hope this clear

Thanks

 

[Allen Browne]

I think that requires some thought about the nature of the link between the
2 fields.

For the example I suggested, one sales fair can give rise to several other
fairs. There is therefore a one-to-many relation. This kind of relation
could not be stored as a single reciprocal relationship like you describe.

If the relation is many-to-many you could model that with a junction table
between 2 copies of the table (if there were always exactly 2 events involvd
in the relation.)

Or, you could specify the group, and have many events grouped together. For
an example of that structure, see tblGroupClient in the screenshot on this
page:
http://allenbrowne.com/AppHuman.html
That's the core table representing a
family/committee/company/some-collection-of-people who belong to a group.
This structure would certainly be the most flexible, and probably the most
applicable tof a reciprocal relation.