Prev: Custom groups moving from assigned to unassigned.
Next: move a primary key to a child
From: KARL DEWEY on 12 May 2010 16:01

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.

--
Build a little, test a little.


"Doctor" wrote:

> 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" wrote:
>
> > Yes, like this --
> > One --------------------- Many
> > Supervisor can have many employees.
> >
> > ResourceID can have many P_O_ResourceID that a part of it.
> >
> > --
> > Build a little, test a little.
> >
> >
> > "Doctor" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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.
From: John W. Vinson on 12 May 2010 16:40
On Wed, 12 May 2010 07:36:01 -0700, Doctor <Doctor(a)discussions.microsoft.com>
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]
From: Doctor on 13 May 2010 09:49
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?

"John W. Vinson" wrote:

> On Wed, 12 May 2010 07:36:01 -0700, Doctor <Doctor(a)discussions.microsoft.com>
> 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]
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Custom groups moving from assigned to unassigned.
Next: move a primary key to a child