Many to Many form

[OwenW]

Forgive me, I am such a novice at this. I am constructing a database to house details of a number of pipe organs, for academic research and not for public use. Although the design of the database itself is relatively simple (although quite complicated enough for me), the number of fields per organ is large and so is the number of instruments that I am collecting. Therefore the design of the form system is hugely important.

Most of the form system has been designed; although I am not 100% sure that I have got the usability right yet, I am at least confident that it will work. The major exception is my many to many relationship.

Essentially, an organ is split into divisions and each division has a number of stops associated with it. Each stop is made up of one or more ranks. However, ranks can be assigned to multiple stops across multiple divisions. Therefore it is a many to many relationship: one stop can have many ranks, one rank can be part of many stops.

I have therefore created a join table to link the two together. I have tried to incorporate a subform into either my ranks or stops forms, but it didn't work very well. I have instead built a simple multiple-items form based on the link table and have embedded it within my organs form, but have hit a snag. I have a lookup for rank and stops, programmed into the table, but need them to only display the ranks and stops associated with the organ in question. At the moment they display all the ranks and stops in their tables. I tried putting in a relationship between the link table and the organs table, but that didn't seem to help. There is already a relationship between the ranks table and the organs table, and an indirect relationship between stops and organs.

I have also found that if I add a new rank or stop then it doesn't come up in my link form until the form is closed and reopened. Does this mean that I need to avoid using a tab for this and instead use a hyperlink or similar?

Apologies for the basic nature of my questions, but I am an organologist, not a database engineer! I enclose a small database which I made to illustrate my problem.

 

[jdraw]

I looked at your tables and relationships. I am not familiar with the subject matter, but based on your description I question the inclusion of OrganID in the Link and Ranks tables.
I think the attached satisfies your description, but then again you know the details better than any reader.
See stump the model to vet your model with your data. Tables and relationships are critical; then design your forms.

 

[OwenW]

OrganID needs to be in the Ranks tables because each rank can be part of only one organ, and that relationship is important. For example, I would want to design a query to look at the average number of ranks per organ within each calendar year. I put OrganID in the link table purely to attempt to solve the problem that I have, which it didn't do.

 

[jdraw]

each rank can be part of only one organ

So 1 organ may have 1 or many ranks?
Let's clarify and get some sample data to design, and test this:

Essentially, an organ is split into divisions and each division has a number of stops associated with it. Each stop is made up of one or more ranks. However, ranks can be assigned to multiple stops across multiple divisions. Therefore it is a many to many relationship: one stop can have many ranks, one rank can be part of many stops.

Also, I'm not following why RankID and StopID (autonumbers) have text values in your display. OK found it Lookup Fields???

 

[OwenW]

Yes, indeed. Let us consider Durham Cathedral (attached). A large instrument like this will have divisions, labelled 'Great', 'Swell' and so on. Each division is associated with a particular keyboard on the organ, although they can be moved around through controls called 'couplers'. Within each division is a number of stops, labelled 'Open Diapason', 'Lieblich Gedackt' and so on. These are a representation of the inside of the organ that the organist uses to decide how to play, and each correspond to a small knob which the organ pulls and pushes to control whether sound emerges. Each stop has a pitch associated with it, based on the length of a representative bottom C pipe measured in feet.

A rank is a set of pipes, with one pipe per note. If there was a 1:1 relationship between rank and stops (i.e. one rank to each stop and one stop to each rank), then the specification I have attached would represent the inside of the organ. However, in the Durham example above, you can see in the Pedal Organ there are a number of stops labelled 'from 1' etc. This indicates that pipes are shared between two different stops, i.e. that one rank is used for more than one stop. This can be done in a variety of different ways and can result in a single stop (such as stop 5 in the Durham example) being utilised at multiple pitches on the same division, or (like stop 92), being utilised under multiple divisions.

In addition, some stops have a roman numeral in place of the pitch number, as in 'Mixture IV' or 'Cymbale III'. That indicates that there are multiple pipes sounding (3 and 4 respectively) for each note. In other words, that stop consists of multiple ranks.0

In some organs, thankfully not the ones that I am dealing with, a very small number of ranks (say 25) can produce an enormous number of stops (in that instance over 200). These are known as 'extension organs' and their design is a discipline in itself. But this example goes to show the distinction between the two terms.

In my database, I need to look at stops and ranks separately. For instance, I will want to track the names of stops over time and the composition of divisions as portrayed on the specification. That is something that can only be done through looking at stops. However, I will also want to look at the overall number of pipes in an instrument, the tonal characteristics of each rank, the balance of tone, and indeed the relationship between stops and ranks, each of which require ranks to be investigated. There are some instances where a query may need to take information from both tables. For example, I thought it would be interesting to have an average pitch for an instrument, and to see how that changes between instruments and over time. Should I use the pitch of the stops or the ranks? It must be the former, because I am dealing with the pitch of the instrument as perceived and played, rather than the instrument as physically present. However, this throws up a difficulty with mixturework, which has multiple ranks per stop. So I would end up constructing a query that would average the pitch of all non-mixture stops, and all mixture-assigned ranks. Not perfect, but it should be good enough.

I'm aware that my project is one in which the concept is rather different from others, and is not one with which anybody is likely to be familiar. I hope that this helps, and I am profoundly grateful for any assistance that anybody can give.

 

[OwenW]

Also, I'm not following why RankID and StopID (autonumbers) have text values in your display. OK found it Lookup Fields???

Yes, I am sure that this is a bad way of doing it, but as you will have worked out, I don't really know what I'm doing. In order to make the link table work practically I needed to match words against words, rather than numbers against numbers, which could so easily go wrong. Having set it up as a foreign key, I thought that the best way round this would be instead to make it a lookup field. Please feel free to tell me that I am being an idiot!

 

[Gasman]

Surely a good few organs could have a Stopped Diapason Rank though?
Also different ranks could have the same Stop Name as well?

Here is the very first DB I created, so not the best I admit , but I needed to link ships with dates, people and ranks.
So a person's rank could change as they progress, as ship would have several dates, people would be in there only once as would the ships.?

The links table controls all that vi my Links form. Each of the four controls for those data items are combos.?

All this supplies the data for http://www.bibby-gazette.co.uk/ via queries and reports as pdf files

 

[OwenW]

Surely a good few organs could have a Stopped Diapason Rank though?
Also different ranks could have the same Stop Name as well?

Yes, but they would be different pipes. In other words, the ranks table is describing physical pipework, rather than nomenclature. In the same way that you may have two Joe Bloggs in your table, you wouldn't combine them because they represent different people. So a Stopped Diapason on one instrument would be different from the Stopped Diapason on another instrument. The same goes for stop nomenclature. There may be any number of Open Diapason stops on an organ, but each of them represent a different, physical, stop knob, even if they don't represent different ranks of pipes.

 

[Pat Hartman]

I needed to match words against words, rather than numbers against numbers

This is a lack of understanding of how relationships work. You seem to have the relationships defined using the PK but you are confusing things by using lookup lists on the tables.

A complex relationship like this is difficult to describe and I am also not a subject matter expert but I am an expert in defining complex relationships. One of the reasons for using autonumbers as the PK is because similarly named components will exist on multiple Organs but just because two organs have a "Swell Organ" doesn't mean that there really is only ONE "Swell Organ" in the world. That means that the relationships are not really m-m, they are 1-m because "Swell Organ" with an ID of 5 exists once and only once on some particular organ even though other organs have components named "Swell Organ".

Now, "Swell Organ" might mean something to you and you might want to know which Organs contain "Swell Organs" and that is a valid search. You would use a combo box on a form that shows the name of the component and that is what you will select but it will search on the ID to find all the organs that have a "Swell Organ"

 

[OwenW]

This is a lack of understanding of how relationships work. You seem to have the relationships defined using the PK but you are confusing things by using lookup lists on the tables.

A complex relationship like this is difficult to describe and I am also not a subject matter expert but I am an expert in defining complex relationships. One of the reasons for using autonumbers as the PK is because similarly named components will exist on multiple Organs but just because two organs have a "Swell Organ" doesn't mean that there really is only ONE "Swell Organ" in the world. That means that the relationships are not really m-m, they are 1-m because "Swell Organ" with an ID of 5 exists once and only once on some particular organ even though other organs have components named "Swell Organ".

Now, "Swell Organ" might mean something to you and you might want to know which Organs contain "Swell Organs" and that is a valid search. You would use a combo box on a form that shows the name of the component and that is what you will select but it will search on the ID to find all the organs that have a "Swell Organ"

I think that the organ thing is causing some confusion, doubtless to my inability to explain thoroughly. I don't think I have misunderstood as fundamentally as you suggest, although I am happy to proved wrong on that. My reason for saying that I needed to match text to text rather than number to number was that otherwise I needed to remember whole strings of numbers in order to populate my join table. I couldn't think of another way of doing it other than by lookup fields. You'll notice though that my relationship is with the primary key, i.e. each 'Open Diapason' that I select is associated with a different primary key, even though that key is not displayed within the lookup. Therefore in the link table I can differentiate between different cells with the same value.

I've attached a renamed database which I hope will be clearer. The analogy is slightly contrived, but hopefully it will do. This is a database tracking how different companies allocate their workers to different tasks. Each company has a number of workers, and a number of divisions. Each division has a number of different shifts available. Each worker can work more than shift in more than one division. Equally, each shift has a number of workers in it. No worker can work for more than one company. There is therefore a many to many relationship between shifts and employees. My question is how to construct a form to input data into the join table without having to scroll through a bewilderingly long list of shifts and people, whether displayed as text or numbers.

I've kept CompanyID in the join table because that enables the form to only display links between individuals and shifts for a single company at a time. However, the dropdowns display information from both companies.

I am very grateful for all of your help, and I sincerely apologise for my own cluelessness! It has been a long time since I last taught myself how to use a piece of software.

 

[Uncle Gizmo]

I can't see anything wrong with the sample database you have provided. I became interested in the thread because I have a Blog on many-to-many and I thought that I should post a link to it.

Many to Many Relationship – Nifty Access

www.niftyaccess.com

I don't think you really need the sample database provided HERE:- https://gum.co/ManyToMany1 but if you do, you can get a free copy by using the coupon code:- czpb22o

 

[OwenW]

I can't see anything wrong with the sample database you have provided. I became interested in the thread because I have a Blog on many-to-many and I thought that I should post a link to it.

Thank you for that. You seem to have come up with a similar solution to me. To put my problem in the shape of your example database, each hobby becomes an after school club, and each student and after school club is associated with a specific school. How do you limit the content of the drop down box to the hobbies and students of one particular school?

 

[jdraw]

OwenW,

Just looked at your ManyToMany2 after reading comments by others.
Given that Tony (UG) OK'd your design, I tried using your dataentry form to enter a new Person (Polly Carbonate)
into shift 2 for Division "Design" for Company 2.
A couple of issues:
-unclear how to select a Division for a specific company?
-there should be a mechanism(cascading combo??) to restrict Shift selectables to the Company_Division involved?

 

[Uncle Gizmo]

each student and after school club is associated with a specific school.

To achieve this I think I would first try adding a new table called "schools". Add a new field "fSchool" to each hobby and student table, enter the school that is associated with the student or hobby in the field "fSchool"and then use that field to restrict the records shown to just students and hobbies from the designated school.

 

[OwenW]

-unclear how to select a Division for a specific company?
-there should be a mechanism(cascading combo??) to restrict Shift selectables to the Company_Division involved?

1. I've just been using pgdn to scroll between divisions, because when I tried to insert a multiple items form it wouldn't let me insert another one for some reason.
2. In this instance we don't want to restrict the selectable shifts to divisions, because somebody can be assigned to any division. However, the notion of how to do that is essentially my problem, as I want to restrict the box to results from one company only. Any ideas?

To achieve this I think I would first try adding a new table called "schools". Add a new field "fSchool" to each hobby and student table, enter the school that is associated with the student or hobby in the field "fSchool"and then use that field to restrict the records shown to just students and hobbies from the designated school.

How do I restrict those records? In my Many to Many 2 I had a relationship between company and people, divisions and link, and it didn't restrict the options.

 

[Uncle Gizmo]

Add a combo box in your form that Returns the school you are selecting. Assuming you have two fields in your school table, ID and the name of the school, then you adjust the combobox properties so that at it contains two columns from the the school table. Using column widths you put 0cm for the first column there by hiding it. Use a query for both your your student form and your hobby form which filters on the contents of the combobox.

See video number 6 on my website where I Explain "Using column widths you put 0cm for the first column there by hiding it"

Combo Boxes​

Combo Box – Nifty Access

www.niftyaccess.com

Adding a Checklist 4 - Nifty Access​

 

[OwenW]

Add a combo box in your form that Returns the school you are selecting. Assuming you have two fields in your school table, ID and the name of the school, then you adjust the combobox properties so that at it contains two columns from the the school table. Using column widths you put 0cm for the first column there by hiding it. Use a query for both your your student form and your hobby form which filters on the contents of the combobox.

Aha, you use a query! That makes a lot of sense. Would you mind going into more detail about how one does that?

 

[jdraw]

OwenW,

If you are adding a new Person, that person has to be assigned to a Company and Division; then to a Shift specific to that Company and Division. Your form doesn't appear to allow selecting a Division assignment for a new Person. As has been mentioned, cascading combos are the means to selectively refine options.

 

[Pat Hartman]

otherwise I needed to remember whole strings of numbers in order to populate my join table.

Not true. That is what combo boxes are for. You will NEVER even see the numbers unless you open the tables directly and even as the developer, you should NOT be updating the tables directly. You should be using the form interface because that is where the code that validates data lives. Using lookups on the tables is what is allowing you to follow this poor practice of updating the tables directly. Remove the lookups (there's lots of info about why they are a problem so you don't have to believe me. Look for yourself). Use combos on forms and you will have the same nice experience of choosing by text rather than by number.

The bridge game is about to start so I'll look at your new example later.

 

[OwenW]

If you are adding a new Person, that person has to be assigned to a Company and Division; then to a Shift specific to that Company and Division. Your form doesn't appear to allow selecting a Division assignment for a new Person. As has been mentioned, cascading combos are the means to selectively refine options.

In this example, that is deliberate, because people can serve in shifts within more than one division. I shall look up cascading combos.