If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Cross References Help
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
Cross References Help
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. |
#4
|
|||
|
|||
Cross References Help
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. |
#5
|
|||
|
|||
Cross References Help
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. |
#6
|
|||
|
|||
Cross References Help
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. |
#7
|
|||
|
|||
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] |
#8
|
|||
|
|||
Cross References Help
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 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] . |
Thread Tools | |
Display Modes | |
|
|