Can you turn a one to many relationship between two tables into a many to many relati (1 Viewer)

Apples241

Registered User.
Local time
Today, 07:23
Joined
Aug 29, 2016
Messages
54
I didn’t know how else to title this--and I am in the process of learning some things in Access--but I have a database where I have, I believe, a one to many relationship between two tables. I have created a main form that is based on tblHub and a subform based on tblNode with a combo box on the main form where you can select a hub to list one to many nodes in the subform.

I would not be able to reverse this and base the main form on the tblNode and the subform on the tblHub. If I were to do that and select a node in the combo box on the main form, I would only get one hub record in the subform.

Below is a picture of how my relationships are now.
https://drive.google.com/open?id=0B9IyKJSJ52ghMUJSaWVKSFlMSnc

I want to answer a question someone has asked me so I want to make sure I answer correctly: I will never reverse this relationship where I will need to base the main form on the tblNode and the subform on the tblHub. Can I make the relationship a many to many relationship between hub and node and not cause any problems down the road for me? I mean, what does it matter if I will never create forms with the reverse relationship.

I want to do this b/c there is a post on how to search a database and it works on many to many database but not on a one to many. When I tried to find a node in my subform, it would only find the nodes that were currently being filtered by the hub from the main form. In other words, it wouldn’t find nodes under other hubs, and I would like to use the search method and wonder if I can just convert my database to a many to many, adding a junction table and moving the attributes from tblNode to the junction table, of course. I just don't see the harm in it if I'm never going to reverse and have nodes on a main form and hubs on a subform anyway.

I would appreciate the help. Thank you.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:23
Joined
Jan 23, 2006
Messages
15,362
When you implement a Many to Many relationship in a relational database, you convert the Many to Many to two(2) 1 to Many relationships.

Example with Students and Subjects

Suppose you have 200 Students, and there are 178 different Subjects

Now there is logically a Many to Many between Student and Subject, but you really want to implement
which Student studies which Subject.

Students----->StudentStudiesSubject<----Subject

where ----> represents a 1 to Many relationship
 

sneuberg

AWF VIP
Local time
Today, 07:23
Joined
Oct 17, 2014
Messages
3,506
if you want to display something other than just the nodes that belong to that hub in the subform I wouldn't denormalized the structure of the data to get there. If in the real world the same node can't be assigned to more than one hub you shouldn't change the structure to one where that would be allowed. Also it's harder to set up forms for many to many relationships.

Please tell us what you need to do, i.e., how you want the subform to work, and I'm pretty sure we can give you a solution that doesn't involve destroying your structure.
 

Apples241

Registered User.
Local time
Today, 07:23
Joined
Aug 29, 2016
Messages
54
Thank you for replying, sneuberg. I have the subform working as I want it in day to day use. It is a continuous subform filtered by the combo box from the main form (see pic of a test form/subform below).

https://drive.google.com/open?id=0B9IyKJSJ52ghamVncDJrbDM1VlU

I now need to find records in the subform, and I thought I could just put a text box on the main form and type in any value and it would go to that value in the subform, like when you use Find and Replace in Word or Excel. (I can use Find and Replace to find values in the subform but only for records under the hub currently being filtered.)

I found two projects online that will search, or rather, filter data. One uses a combo box in the main form to filter data, but that project is based on a many to many set up. This is why my original question came up b/c I wanted to see if I could switch my database to many to many, too, without it causing any issues later for me.

The other project I have not finished looking at, but it looks like they make a whole new form based on a query of the fields you want from your subform and then adds text boxes on that new form to either search or filter the data.

I wanted to avoid having users to open a new form that will look like a recreation of the subform they are currently on in order to search for values.

Am I correcting in understanding you that I’m pretty much going to have to go this route in order to search for values in my database since you mentioned I should not change the structure of my database since in the real world it is a one to many between hub and node?

Thank you.
 

Apples241

Registered User.
Local time
Today, 07:23
Joined
Aug 29, 2016
Messages
54
Thank you, jdraw. I appreciate your reply. Yes, I was just wondering if it would be ok to change my structure to a many to many for the purpose of being able to use a project I found on the internet to search for records in my subform (b/c the project is based on a many to many set up). I wanted to see if I could do this even though my data is really one to many in theory, in my view anyway. Thank you, again.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:23
Joined
Jan 23, 2006
Messages
15,362
Please show us the link to the project you referenced.
Also, we need to know more about database structure, tables and relationships.

Describe in simple terms what you are trying to search and what you do with the result(s).
Sample data to illustrate the issue would also be useful.

RE your attachment.
I'm not sure from the picture what it all represents. In Access form and subform are typically used to process/interact/display records from tables that have a 1 to Many relationship.
Form holds the 1 side records and Subform holds the related Many records.
 

sneuberg

AWF VIP
Local time
Today, 07:23
Joined
Oct 17, 2014
Messages
3,506
I think you could make a form without a subform that would do what you want. Just start by making a query of the fields you want to search and make a continuous form out of it. You can put the combo box and text box for search in the form header. You can make the row source of one of the search combo boxes the tblHub table and be able to display the same results are getting with the form/subform you have not. I suggest looking at this Photograph Coordinates1 (database that was put together by JHB in post 7 of this thread.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:23
Joined
Sep 12, 2006
Messages
15,613
wrt many-to-many:

a relational database cannot represent a many to many relationship. It MUST be split into 2 one-to-many relationships

eg - take a common example of educational courses and students.

A course has many students. Students take many courses. This is many to many. In order ot do this you need 3 tables

Students
StudentsCourseRegistrations
Courses

and 2 1 to many relationships

Students 1-many Registrations many-1 Courses

ie - a student and a course are not directly related, only indirectly through the registrations table.


-----
you just cannot model a many to many in a rdb. What happens is that you get a cross-product or Cartesian join. Every member of one table is matched to every member of the other table, which is not what you are trying to achieve, and not useful.
 

HiTechCoach

Well-known member
Local time
Today, 09:23
Joined
Mar 6, 2006
Messages
4,357
I agree with Steve.

I would NEVER change a properly designed and normalization table design to make a form or report easier. It will soon cause many other headaches.

What you want can be done without any table design/relationship changes.

I would urge you to consider a pop up sear form.

See: http://www.allenbrowne.com/ser-62.html

 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:23
Joined
Feb 19, 2013
Messages
16,553
When I tried to find a node in my subform, it would only find the nodes that were currently being filtered by the hub from the main form.
it depends on how your mainform works, but assuming the recordsource to the main form is tblHubs, one way of handling this in your existing form is to have an unbound textbox (called say txtNode) so you can enter the node you want to find.

then either in the after update event of the textbox, or perhaps the click event for a button have

Code:
me.filter="HubID = " & dlookup("HubID","TblNode","NodeID=" & me.txtNode)
me.filteron=true
you will probably need to change the criteria of the dlookup depending on what the user enters to find another node, and don't forget the single quotes if it is a text value to be entered
 

Apples241

Registered User.
Local time
Today, 07:23
Joined
Aug 29, 2016
Messages
54
sneuberg, you have been so helpful. I appreciate you answering my question and providing a reference and sample database as a guide. Thank you so much.

I will investigate this and the other answers and let you all know the outcome.

By the way, I see from your reply and one or two others that I will need to just go ahead and create another form to perform the search/filter on. Thank you for answering my questions.

P.S. Pardon the late reply, I was preoccupied Friday and this weekend.
 
Last edited:

Apples241

Registered User.
Local time
Today, 07:23
Joined
Aug 29, 2016
Messages
54
Please show us the link to the project you referenced.
Also, we need to know more about database structure, tables and relationships.

Describe in simple terms what you are trying to search and what you do with the result(s).
Sample data to illustrate the issue would also be useful.

RE your attachment.
I'm not sure from the picture what it all represents. In Access form and subform are typically used to process/interact/display records from tables that have a 1 to Many relationship.
Form holds the 1 side records and Subform holds the related Many records.

Thank you, jdraw. I appreciate your help. I'm going to investigate these other replies before uploading the db. I will reply to everyone with the outcome of my tests. Thanks, again.
 

Apples241

Registered User.
Local time
Today, 07:23
Joined
Aug 29, 2016
Messages
54
I agree with Steve.

I would NEVER change a properly designed and normalization table design to make a form or report easier. It will soon cause many other headaches.

What you want can be done without any table design/relationship changes.

I would urge you to consider a pop up sear form.

See: http://www.allenbrowne.com/ser-62.html


Thank you, so much HiTechCoach. I will look this over as I mentioned in the other replies, and I will be letting you know what the outcome of the different replies are. I appreciate your input and example/reference.
 

Apples241

Registered User.
Local time
Today, 07:23
Joined
Aug 29, 2016
Messages
54
it depends on how your mainform works, but assuming the recordsource to the main form is tblHubs, one way of handling this in your existing form is to have an unbound textbox (called say txtNode) so you can enter the node you want to find.

then either in the after update event of the textbox, or perhaps the click event for a button have

Code:
me.filter="HubID = " & dlookup("HubID","TblNode","NodeID=" & me.txtNode)
me.filteron=true
you will probably need to change the criteria of the dlookup depending on what the user enters to find another node, and don't forget the single quotes if it is a text value to be entered

Thank you very much, CJ_London. I appreciate you replying and providing an example/alternative of what I can do. I mentioned to the others, I am going to try all of these out and reply back re: the outcome. Thank you again for your help. (Pardon the late reply.)
 

Apples241

Registered User.
Local time
Today, 07:23
Joined
Aug 29, 2016
Messages
54
Thank you, gemma-the-husky for your input. I appreciate your help.
 

Users who are viewing this thread

Top Bottom