View Single Post
  #2  
Old May 12th, 2010, 03:55 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Cross References Help

I think a self-join will do what you need. Change the table to this --
tblResources
-ResourceID - Primary key
-ResourceText
-P_O_ResourceID - foreign key

In the Relationship window put your table twice. Access will add a suffix
of '_1' to the table name. Create a one-to-many relationship from first
table ResourceID to second table P_O_ResourceID and select Referential
Integerity and Cascade Update.

P_O_ResourceID says it is Part Of the higher ResourceID. This is like an
employee table show who is the supervisor.


--
Build a little, test a little.


"Doctor" wrote:

I'm sure this question has an easy answer. Just can't figure it out on my own.

How do I design a situation where I can have multiple cross-references
created for my records?

My current setup:
tblResources
-ResourceID
-ResourceText

tblCrossReferences
-ParentResourceID
-ChildResourceID

I have a many to many relationship. But here is where it falls apart.

If I am on record 1 and I create cross references to records 2, 3, and 4 in
the cross references subform, then I navigate on the main form to record 2, I
want to be able to see the cross reference linking records 1 and 2. But since
the sfrmCrossReferences is linked to the ParentResourceID, it doesn't show
that these two resources have been linked when I am on record 2.

Is there a way to show this?

Any help would sure be greatly appreciated.