Cross References Help
On Wed, 12 May 2010 07:36:01 -0700, 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.
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
--
John W. Vinson [MVP]
|