Cross References Help

[Doctor]

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.

 

[KARL DEWEY]

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.

 

[Doctor]

Does your idea work for multiple cross references? Record 1 to 2, and record
1 to 4, and record 2 to 5, etc...

 

[KARL DEWEY]

Yes, like this --
One --------------------- Many
Supervisor can have many employees.

ResourceID can have many P_O_ResourceID that a part of it.

 

[Doctor]

Karl, thanks for your response. I have done what you suggested, but I can't
get this to do what I need it to do.
How do I design the subform to accomplish my goal: (in my original question
I stated that if I am in record 1 and I create a cross reference to record 2.
Then when I navigate to record 2, I want to be able to see that record 2 is
cross-referenced to record 1.)

Please forgive me for not being able to wrap my head around your suggestion.

 

[KARL DEWEY]

Form for ResourceID and continous subform for P_O_ResourceID. Set
Master/Child links using ResourceID. Use a combo to select the ResourceID of
the subordinates.

It will only display subordinates in the subform. When you move the main
form to a record that is a subordinate the subform would show any records
that are subordeinate to it but not its own supervisor.

With some work you probably could but I never needed that type of display.

 

[John W. Vinson]

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.

You may need to join the crossref table both ways and use a UNION query to
combine them:

SELECT A.ResourceText, B.ResourceText
FROM (tblResources AS A Inner Join tblCrossReferences
ON tblCrossReferences.ParentResourceID = A.ResourceID)
INNER JOIN tblResources AS B ON B.ResourceID =
tblCrossReferences.ChildResourceID
UNION
SELECT A.ResourceText, B.ResourceText
FROM (tblResources AS A Inner Join tblCrossReferences
ON tblCrossReferences.ChildResourceID = A.ResourceID)
INNER JOIN tblResources AS B ON B.ResourceID =
tblCrossReferences.ParentResourceID

 

[Doctor]

I thought about that. But didn't end up doing that because I didn't think
that a form based on a union query would be updateable.

It seems like my best option at this point is to create two subforms. One
for entering new cross references, and another one based on a union query for
displaying them.

Is this a good way to do this?