Solved

Object view of items with many to many relationship

Posted on 2010-09-03
19
484 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
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
 
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 250 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

15 Experts available now in Live!

Get 1:1 Help Now