display groups in Relationships display

bkelly

Active Member
Joined
Jan 28, 2005
Messages
446
Environment: Windows 7- 64 bit, Access 2010.
Concept: Relationship diagram.

After setting up the diagram to show tbl_Issues and its direct connects, another area of that display is setup to show tbl_Activity_&_Comments. It has a many to one relationship with tbl_Issues (shown in the first group) and a many to one with tbl_Activity_Type.

In the second grouping of the Relationship diagram, the relationship between Activity (shortened name) and Type is shown. I am unable to get the diagram to show the relationship between Activity and Issues. This would be the second display of that particular relationship.

Is there some way to get a relationship to be displayed multiple times in the Relationships display?
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
You can always add a table twice ... is that what you are looking for? Then you could show it's relationship(s) more than once. Though I've never had to do this in practice (I usually put my complete DB diagrams in Visio rather than using the Access report to show relationships). The real question is if the relationships are correct, of course. Why is Activities related to both Issues and Types in such as way as to be difficult to show in the relationships window?
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
If you add the table to the view a second time you will get the table with a _1 suffix. You can do that as many times as you need.

Denis
 

bkelly

Active Member
Joined
Jan 28, 2005
Messages
446
You can always add a table twice ... is that what you are looking for? Then you could show it's relationship(s) more than once. Though I've never had to do this in practice (I usually put my complete DB diagrams in Visio rather than using the Access report to show relationships). The real question is if the relationships are correct, of course. Why is Activities related to both Issues and Types in such as way as to be difficult to show in the relationships window?

In the first grouping tbl_Issues is shown with just its direct connects. The second grouping centers around tbl_Activity_&_Comments. The two tables are both present with an _1, but the relationship is not shown.

After closing the database and opening the next day, the third grouping now has relationships drawn back up to the first, crossing over those in the second group. Those were not there when the view was last closed. They are accurate, but I don't want the drawing cluttered with that particular relationship in that particular place on the drawing. When I right click the relationship the options displayed are Edit Relationship and Delete. There is no option "Hide Relationship" or "Show Relationship."

When illustrating a concept, it is not necessary to show ALL the facts, just those currently being discussed.
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Concerning "Hide" or "Show" a relationship, In Access' relationship window, I don't believe it's possible to show a two tables that have a relationship without also showing the relationship.

I'm not sure what the rest of your question is about. I don't see a problem showing a table twice. In my test sample below, table 1 is related to table 2 and table 3. In the first group, we only show the relationship to table 2. In the second we show the relationship to both table 2 and table 3. BTW I think the relationships of the tables are relevant facts since if they are known I or someone else can replicate the structure and see if we can create the display that you are looking for.

<img alt="relationships" src="http://northernocean.net/etc/mrexcel/20120629_rel.jpg" />



Edit:
Taking a second stab at this I made sure a relation was defined many-to-one with referential integrity, and I added the tables after the relationship was defined and saved. Oddly enough I'm definitely wrong about the relationship always appearing (the two copies do not have a relationship showing up). I find that actually disconcerting - what does it mean for the two tables "not" to have a relationship when the actually *do* have one, and what would happen if you tried to create one again (presumably nothing, I guess) - but seems weird putting the same relationship on the same two tables a second time. I still recommend using the relationship window to create relationships and using Visio or another diagramming tool to create diagrams (both are good at these respective tasks - actually if you want to be super cool you can use Visio to create relationships *too* but I think you need the premium version for that).


<img alt="relationships" src="http://northernocean.net/etc/mrexcel/20120629_rel2.jpg" />
 
Last edited:

bkelly

Active Member
Joined
Jan 28, 2005
Messages
446
xenou,
First, Thank you for taking the time to analyze my question and to test out the theory. I appreciate your time.
You do see the same symptom as I. When trying to create the relationship a second time Access replies that the relationship already exists and does not create it agan.

For benifit of feedback to Microsoft
I am learning Access on my own at home and do not have Visio. It seems like a nice utility, but also appears to be needed only because Microsoft has ignored a viable need. That is to show simplified diagrams. I think they should have the ability to create a relationship figure, save it by name, and recall it when needed.

Again, thank you for your time.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

I guess I still don't understand your problem. This is now the same tables as in my previous post (there there are no relationships shown in the "second group" and here we show the relationships in the "second group"). Seems to work fine for me.

<img alt="relationships" src="http://northernocean.net/etc/mrexcel/20120630_rels.jpg" />
 

bkelly

Active Member
Joined
Jan 28, 2005
Messages
446
Hello xenou,
First, to refresh my goal. Relationships get complex and I want to show some key tables with simplified diagrams. I may wish to show tbl_1 and tbl_2 and a particular relationship and relationship A between the two tables, maybe shown in say the upper half of the figure. Tables tbl_3 and tbl_4 and their relationships may also be shown. In this figure, or more correctly, this section of the Access Relationships figure, tbl_6, tbl_7 and tbl_8 are not shown, for simplicity.

In another area, maybe below the just described figure, tbl_1 and tbl_2 may be shown again but without tables 3 and 4, but with tables 6, and 7. Relationship A is also to be shown in this area. That means it should be shown twice.

The core problem as I perceive it is that in Access there is only one Relationship display. I don’t see a method of displaying a relationship diagram and saving it by name such as R1, then recalling it later. And in a single relationship drawing, it appears that, for example relationship A is displayed only one time.

Changing perspective to this thread. In post #5, I first thought you had shown how to accomplish the above task. However, on closer inspection, and on performing a test, this appears to work with a one to one relationship. But it does not work with a one to many.

If that is the case then I have discovered, what I perceive to be, a limitation of Access’s Relationships display. Is this perception correct? If so, then that is just the way it is.

BTW: How do you insert your figures?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
The core problem as I perceive it is that in Access there is only one Relationship display. I don’t see a method of displaying a relationship diagram and saving it by name such as R1, then recalling it later.

On this point I think technically that you are correct, it's not possible. However, I think you can create a report from a relationship diagram, and possibly those reports can be saved. But then you are dealing with the frustration of Access reports. This last, however, is not purely an Access problem. For example, using Crystal Reports, is not a ball of yarn either.

Changing perspective to this thread. In post #5, I first thought you had shown how to accomplish the above task. However, on closer inspection, and on performing a test, this appears to work with a one to one relationship. But it does not work with a one to many.

I seem to be unable to comprehend the problem correctly :( In my post seven I have two "groups" or "sections". One group shows tables one and two, with a one-to-many relationship. The other also shows tables one and two, also with a one-to-many relationship. So it seems possible to show the same table in more than one group, with any other tables you want to show (or not show) and any other relationships you want to show (or not show). But I don't believe these are documented features, or at least I doubt that we are seeing anything other than a side effect. My experience was that when adding a table that was already added, it came without relationships displayed, and then I could put them in, or not, as I liked. If this was intentional then somebody as MS did in fact anticipate your need and provided at least the bare minimum to get your there (!). I think multiple relationship windows might not be feasible considering a main reason for the window is to view, create and edit relationships. What would it mean if one window has a relationship and in another you remove it -- there's probably a bit of under the hood issues resulting and the integrity of diagrams and/or relationships defined in multiple places would be difficult to guarantee.

Edit: I created that diagram in Access 2007, btw. Edit again: to your last question, I show the figures by uploading images to my website and putting an html image tag in the post with a link to the image.
 
Last edited:

bkelly

Active Member
Joined
Jan 28, 2005
Messages
446
Hello xenou,
You are correct and I see an error I made. Your post 7 does do what I wanted and I missed that. How did you do that? When I show the tables a second time, the relationship does not show up in the second pair.

Regarding the figures: I can upload a figure to my web site then reference it and it will show up in the post. However, if at some time in the future I remove the figure from the web sit, the link is broken making the thread difficult to understand. Is that correct? If so, I will remain reluctant to link to figures from a web site.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,387
Messages
5,595,884
Members
414,029
Latest member
mrwilker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top