Solved

Object view of items with many to many relationship

Posted on 2010-09-03
19
468 Views
Last Modified: 2012-05-10
Hi

How items with many to many relationship can be viewed in Object view on MS Access form similar to tree view of hierarchical data having one to many relationship. And is it possible to add drag and re-arrange option.

As shown in the diagram Entity C is owned by A as well as by B for example.

Thanks

Dia.jpg
0
Comment
Question by:versatile22
  • 7
  • 7
  • 5
19 Comments
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 250 total points
Comment Utility
I don't know if this will be of help, but see my Access Archon #s 79 and 178
http://www.helenfeddema.com/Files/accarch79.zip
http://www.helenfeddema.com/Files/accarch178.zip
Here is a screen shot of a form with a many-to-many relationship;

Many-to-Many-Relationship-Form.jpg
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Like Helen I am not sure it this is what you are looking for but...

This will depend on if the One to many is "Horizontal" or "Hierarchical"

If your One to many is "Hierarchical" (nested) then you have 7 nested levels available.
(http://office.microsoft.com/en-us/access-help/access-2007-specifications-HA010030739.aspx)

If your structure is Horizontal:
One Customer-->Many Orders
One Customer-->Many Employees
One Customer--> Many Complaints
You can create your 1 customer form and insert as many "many" subforms as as you like.


Obviously at about 3 subforms, the Purpose of your form, in either case, becomes a bit fuzzy, as it is displaying perhaps too much data.

But that is up to you as the developer to decide.

;-)

JeffCoachman
0
 
LVL 2

Author Comment

by:versatile22
Comment Utility
Hi All

Thanks for the comments. Actually I am looking to have one form which displays all the items with their relationship with each other similar to the one shown in my diagram.

To elaborate more, I have a table of companies and then a LINK TABLE which shows the ownership of each company in other. For example as per the diagram, company A, B & D has invested certain percentages in company C which is shown on the arrows. In other words company C is owned by A, B, and D. I am looking for Tree view control for all the companies similar to the one used for items having one to many relationship. Please refer to below link which can be used for Hierarchical (one to many) relationship:

http://support.microsoft.com/kb/209898

Is it possible to tweak the above to produce a form showing all the companies with their relationship to each other exactly as per the diagram.

Thanks
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
In the diagram you have squares labeled A, B, C and D, presumably representing subforms.  What do you want to see in each of these subforms?
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
If you can prepare a mockup figure with dummy data, that would be helpful.  Also, a sample database is really needed for us to do anything but give suggestions.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
Also, you mention a TreeView control, which is a totally different interface than the four boxes (subforms) in your figure.  Where is the TreeView control used?  TreeView controls are great for hierarchical data (one-to-many relationships), but probably not much use for many-to-many relationships.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
Comment Utility
What also makes it complex is that "theoretically" company "C' could own some of company A, B or D...
In other words, you can have any combination of companies owning one another.

You may also want t look into "Pedigree" structured database structures/Self Joins:
http://allenbrowne.com/ser-06.html
http://jhered.oxfordjournals.org/content/96/4/465.full
http://www.exetersoftware.com/cat/cyrillic/cyrillic.html


But like Helen, I can't see where a Treeview control would help you here...
It will show Company A then it's ownership in any of the other companies.
So it always starts with "One" entity.
In your scenario you would need two (or more) "Related" treeviews to show that both company A and Company B could own part of company C.

IMHO, your best bet would be to keep this simple and just show one Hierarchical treeview for each company.
Or simply use a Main form with a combobox to select a company and see all the directly related Ownerships, but not necessarily trace those relationship directly.
In other words, you would have a Min form where you would select a company, then follow their ownership.
Then  you would go back to the main form and select another company and display their relationships.
So with this interface you could trace the full hierarchy of Company A's ownerships.
If at any point in this hierarchy Company B is displayed, you could see all of it's (company B's) relationships from that point on. (not starting at the Top for company B.
Make sense?

Finally I will ask what you are using as a reference for this interface?
Where have you seen this structure graphically represented.
If there is a company that has developed this type of interface, then perhaps you could contact them directly.

;-)

JeffCoachman
0
 
LVL 2

Author Comment

by:versatile22
Comment Utility
Hi

Thanks for the continued support. Apology for being unable to explain my point. I have prepared a mocked database which contains two tables:
CompaniesListing : Which lists all the companies
CompaniesGroupStructure : Which serves a link table to explain the relationship between each company

Now I am looking for a dynamic form where I can see graphically Companies Group structure as shown on the Solution Required form in the attached database. Currently I have inserted image on the form but anything will be acceptable which produces the same output.

Thanks


Sample.mdb
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
Also, for the relationships between companies, there is another issue.  In my sample database of scouts and badges, you can select a scout's record and select one or more badges for him, or select a badge record and select one or more scouts who have earned that badge.  It doesn't matter which -- the scout has earned that badge.  However, for company ownership, it does matter whether company A owns company B, or vice versa.  So the direction of ownership needs to be indicated in some manner.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Again, as Helen stated, I honestly don't think a Treeview is appropriate here.
It is simply not designed to display data in the way you are requesting.

I would use a basic Main/Subform/Subform, ...etc

Perhaps Helen can help your further with this...

JeffCoachman
0
 
LVL 2

Author Comment

by:versatile22
Comment Utility
Hi

The structure I am asking prevails in most of the Group of companies. And why I am looking for graphical so that I can review the structure of all companies in a group.

Arrows in my diagram somewhat represents the direction of ownership.

Any how thanks for your help so far. What alternative you recommend to see all group companies with their assocaited relationship.

Thanks
0
 
LVL 2

Author Comment

by:versatile22
Comment Utility
Hi boag2000:

I am not insisting on Treeview, anything (reports, forms etc) will suffice which will enble me to Visaulize the above hierarchy.

Any ideas how to start the above will be highly appreciated.

Best Regards
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<Any ideas how to start the above>
What "above" specifically are you referring to?
0
 
LVL 2

Author Comment

by:versatile22
Comment Utility
Hi

I meant the above hierarchy visualization.

Best Regards,
Atif
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Nobody mentioned "hierarchy visualization"?

What post number, specifically, are you referring to?
0
 
LVL 2

Author Comment

by:versatile22
Comment Utility
Hi Boag

I am referring to my post ID 26450030 and requesting for an idea wherby i can see all items altogether at one place with their relationshiop with each other.

Thanks
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
I thought that Helen and I made it clear that there was no easy way to do what you are asking in MS Access...

<TreeView controls are great for hierarchical data (one-to-many relationships), but probably not much use for many-to-many relationships.>

See Helen's example here: 33596416
See my post 33604704 for possible alternatives

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Yes,  even without using a Treeview control, displaying the relationships in the way you are requesting would probably require something custom...
0
 
LVL 2

Author Closing Comment

by:versatile22
Comment Utility
Hi

Not a complete solution I was looking for. Just ideas were proposed giving partial concepts only.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now