A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Cross References Help



 
 
Thread Tools Display Modes
  #1  
Old May 12th, 2010, 03:36 PM posted to microsoft.public.access.tablesdbdesign
Doctor
external usenet poster
 
Posts: 75
Default 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.
Ads
  #2  
Old May 12th, 2010, 03:55 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old May 12th, 2010, 06:25 PM posted to microsoft.public.access.tablesdbdesign
Doctor
external usenet poster
 
Posts: 75
Default 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  
Old May 12th, 2010, 06:40 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old May 12th, 2010, 07:47 PM posted to microsoft.public.access.tablesdbdesign
Doctor
external usenet poster
 
Posts: 75
Default 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  
Old May 12th, 2010, 09:01 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old May 12th, 2010, 09:40 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old May 13th, 2010, 02:49 PM posted to microsoft.public.access.tablesdbdesign
Doctor
external usenet poster
 
Posts: 75
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:13 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 OfficeFrustration.
The comments are property of their posters.