?
Solved

Object view of items with many to many relationship

Posted on 2010-09-03
19
Medium Priority
?
520 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 5
19 Comments
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 750 total points
ID: 33596416
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
ID: 33599145
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
ID: 33602326
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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 31

Expert Comment

by:Helen Feddema
ID: 33603152
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
ID: 33603162
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
ID: 33603168
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 750 total points
ID: 33604704
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
ID: 33606088
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
ID: 33607221
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33608697
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
ID: 33609621
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
ID: 33635450
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
ID: 33638653
<Any ideas how to start the above>
What "above" specifically are you referring to?
0
 
LVL 2

Author Comment

by:versatile22
ID: 33661308
Hi

I meant the above hierarchy visualization.

Best Regards,
Atif
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33668019
Nobody mentioned "hierarchy visualization"?

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

Author Comment

by:versatile22
ID: 33669771
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
ID: 33670180
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
ID: 33670189
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
ID: 33946851
Hi

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses
Course of the Month13 days, 2 hours left to enroll

777 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