Cross References Help
in [Tables]
|
From: Doctor on 12 May 2010 10:36 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: KARL DEWEY on 12 May 2010 10:55 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: Doctor on 12 May 2010 13:25 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: KARL DEWEY on 12 May 2010 13:40 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: Doctor on 12 May 2010 14:47 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.
|
Next
|
Last
Pages: 1 2 Prev: Custom groups moving from assigned to unassigned. Next: move a primary key to a child |