Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-09-2017, 12:55 PM   #1
Apples241
Newly Registered User
 
Join Date: Aug 2016
Posts: 54
Thanks: 28
Thanked 2 Times in 2 Posts
Apples241 is on a distinguished road
Can you turn a one to many relationship between two tables into a many to many relati

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=0B9...UJSaWVKSFlMSnc

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.

Apples241 is offline   Reply With Quote
Old 02-09-2017, 01:05 PM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,903
Thanks: 73
Thanked 1,963 Times in 1,911 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Can you turn a one to many relationship between two tables into a many to many re

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
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
Apples241 (02-10-2017)
Old 02-09-2017, 01:13 PM   #3
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,507
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Can you turn a one to many relationship between two tables into a many to many re

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.

__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
The Following User Says Thank You to sneuberg For This Useful Post:
Apples241 (02-10-2017)
Old 02-10-2017, 07:14 AM   #4
Apples241
Newly Registered User
 
Join Date: Aug 2016
Posts: 54
Thanks: 28
Thanked 2 Times in 2 Posts
Apples241 is on a distinguished road
Re: Can you turn a one to many relationship between two tables into a many to many re

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=0B9...mVncDJrbDM1VlU

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 is offline   Reply With Quote
Old 02-10-2017, 07:18 AM   #5
Apples241
Newly Registered User
 
Join Date: Aug 2016
Posts: 54
Thanks: 28
Thanked 2 Times in 2 Posts
Apples241 is on a distinguished road
Re: Can you turn a one to many relationship between two tables into a many to many re

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.
Apples241 is offline   Reply With Quote
Old 02-10-2017, 07:44 AM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,903
Thanks: 73
Thanked 1,963 Times in 1,911 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Can you turn a one to many relationship between two tables into a many to many re

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
Apples241 (02-13-2017)
Old 02-10-2017, 08:02 AM   #7
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,507
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Can you turn a one to many relationship between two tables into a many to many re

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.

__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
The Following User Says Thank You to sneuberg For This Useful Post:
Apples241 (02-13-2017)
Old 02-10-2017, 12:47 PM   #8
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,749
Thanks: 55
Thanked 1,021 Times in 987 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Can you turn a one to many relationship between two tables into a many to many re

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.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
The Following User Says Thank You to gemma-the-husky For This Useful Post:
Apples241 (02-13-2017)
Old 02-10-2017, 03:33 PM   #9
HiTechCoach
Newly Registered User
 
HiTechCoach's Avatar
 
Join Date: Mar 2006
Location: Oklahoma City, OK
Posts: 4,253
Thanks: 8
Thanked 156 Times in 144 Posts
HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough
Re: Can you turn a one to many relationship between two tables into a many to many re

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

__________________
Boyd Trimmell
Specializing in Accounting, CRM, and Business solutions
Microsoft Access MVP 2010-2015 , aka
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
HiTechCoach is offline   Reply With Quote
The Following User Says Thank You to HiTechCoach For This Useful Post:
Apples241 (02-13-2017)
Old 02-10-2017, 04:39 PM   #10
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,773
Thanks: 40
Thanked 3,494 Times in 3,381 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Can you turn a one to many relationship between two tables into a many to many re

Quote:
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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Apples241 (02-13-2017)
Old 02-13-2017, 07:10 AM   #11
Apples241
Newly Registered User
 
Join Date: Aug 2016
Posts: 54
Thanks: 28
Thanked 2 Times in 2 Posts
Apples241 is on a distinguished road
Re: Can you turn a one to many relationship between two tables into a many to many re

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 by Apples241; 02-13-2017 at 07:16 AM.
Apples241 is offline   Reply With Quote
Old 02-13-2017, 07:13 AM   #12
Apples241
Newly Registered User
 
Join Date: Aug 2016
Posts: 54
Thanks: 28
Thanked 2 Times in 2 Posts
Apples241 is on a distinguished road
Re: Can you turn a one to many relationship between two tables into a many to many re

Quote:
Originally Posted by jdraw View Post
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 is offline   Reply With Quote
Old 02-13-2017, 07:14 AM   #13
Apples241
Newly Registered User
 
Join Date: Aug 2016
Posts: 54
Thanks: 28
Thanked 2 Times in 2 Posts
Apples241 is on a distinguished road
Re: Can you turn a one to many relationship between two tables into a many to many re

Quote:
Originally Posted by HiTechCoach View Post
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 is offline   Reply With Quote
Old 02-13-2017, 07:18 AM   #14
Apples241
Newly Registered User
 
Join Date: Aug 2016
Posts: 54
Thanks: 28
Thanked 2 Times in 2 Posts
Apples241 is on a distinguished road
Re: Can you turn a one to many relationship between two tables into a many to many re

Quote:
Originally Posted by CJ_London View Post
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 is offline   Reply With Quote
Old 02-13-2017, 07:19 AM   #15
Apples241
Newly Registered User
 
Join Date: Aug 2016
Posts: 54
Thanks: 28
Thanked 2 Times in 2 Posts
Apples241 is on a distinguished road
Re: Can you turn a one to many relationship between two tables into a many to many re

Thank you, gemma-the-husky for your input. I appreciate your help.

Apples241 is offline   Reply With Quote
Reply

Tags
access 2013 , many to many relationship , one to many

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Tables and relationship ENEMAL General 3 08-07-2013 06:06 PM
Relationship between two tables Dunyazada Queries 12 11-26-2012 02:30 PM
Relationship between tables zcampos Tables 1 07-18-2011 08:15 PM
Relationship between 2 tables accessman2 Tables 2 10-23-2005 12:40 PM
relationship/tables mhtmarsha Tables 8 07-10-2003 03:49 PM




All times are GMT -8. The time now is 09:49 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World