Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-03-2019, 01:46 AM   #1
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 608
Thanks: 300
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Many to many relationship but with exceptions

Hi,

i want to ask you about exceptions and model tables as many to many relationship with junction table like below:



So specific CustomerTopoSysVersion can have multiple SystemVersions and each of SystemVersion can have multiple OSes.

But i have exceptions here.
Sometimes instead of CustomerTopoSysVersion --> SystemVersions --> OperatingSystems i can have
CustomerTopoSysVersion --> OperatingSystems (not every CustomerTopoSysVersion will have SystemVersion, sometimes i have to skip it)

How do you handle with this kind of situation?
Are you using some kind of artificial string for SystemVersion like "NoMatch" in junction table or something like that or you have another solutions?

Please help,
Jacek
Attached Images
File Type: png Screenshot_12.png (37.4 KB, 110 views)

jaryszek is offline   Reply With Quote
Old 06-03-2019, 05:02 AM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,676
Thanks: 93
Thanked 1,702 Times in 1,575 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Many to many relationship but with exceptions

The U.S. Navy faces a problem like that for the people in this world who have no middle name. They include "(NMN)" as the middle name in any computer records for that person so that the reader who is familiar with Navy ways will see that as (no middle name). I guess you could use "(NVN)" for (no version number)?
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-03-2019, 07:39 AM   #3
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 608
Thanks: 300
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Many to many relationship but with exceptions

Thank you The_Doc_Man.

i can even leave here nulls values but i do not think it is good approach. Is it?

The problem is when i am building main form and subforms i can not see related record to subform when systemVersion is empty.

I create example in attachment.
I am adding for CustomerTopology3 Oeratingsystem like here:



as you can see junction table (operating system field) was fullfilled with nullable key for CustomerTopoSysVersionID:



what i have here is the issue that i will not see this variables when i will refresh form:




I do not why. Maybe some people here are using this method to create forms and subforms?

Please help,
Jacek
Attached Images
File Type: png Screenshot_13.png (90.0 KB, 81 views)
File Type: png Screenshot_14.png (91.6 KB, 80 views)
File Type: jpg Screenshot_16.jpg (51.4 KB, 15 views)
Attached Files
File Type: accdb Database18.accdb (528.0 KB, 9 views)

jaryszek is offline   Reply With Quote
Old 06-03-2019, 10:31 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,676
Thanks: 93
Thanked 1,702 Times in 1,575 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Many to many relationship but with exceptions

I'm a little skeptical of using nulls in that context. I don't think a relationship can be established through a null field. But have to admit I have not tried it.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
jaryszek (06-03-2019)
Old 06-03-2019, 10:49 AM   #5
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,418
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Many to many relationship but with exceptions

Quote:
Sometimes instead of CustomerTopoSysVersion --> SystemVersions --> OperatingSystems i can have
CustomerTopoSysVersion --> OperatingSystems

That doesn't make sense. Normally ( and in this case) junction tables aren't real things, unlike other tables that represent real life entities, junction tables exist to sort out data between other real entity representing tables.



Your system version table contains no real unique data unto itself. It simple connects data from Topsysversiin to Operating systems. The only reason to have data in system version is to link data in the other two tables.

System version simple acts as a traffic cop directing data between this two other tables, it doesn't add any real data itself.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
jaryszek (06-04-2019)
Old 06-03-2019, 11:07 AM   #6
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,235
Thanks: 115
Thanked 3,073 Times in 2,791 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Many to many relationship but with exceptions

Jacek
As a null cannot be compared to anything including another null, you cannot use null values in a relationship to get meaningful output.
Once again I suggest you re-read the threads on nulls from earlier this year as you are largely covering ground that was thoroughly discussed before.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
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.


Website links:
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.
,
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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 06-03-2019, 01:00 PM   #7
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,159
Thanks: 3
Thanked 471 Times in 464 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Many to many relationship but with exceptions

I faced a similar problem when I was asked by a large local government authority to set up a system to manage the issue of permits for organizations/companies and individuals wanting to use public land for events or commercial activities.


Either the permit was associated with the organization which in turn had an employee/member as the contact, or with an individual in their own right.


I stored two foreign keys in the permits table, OrganizationID and ContactID and created a "dummy" organization "Not Applicable". Selecting an Organization did filter the Contacts combo, except when N/A was chosen.

Cronk is offline   Reply With Quote
The Following User Says Thank You to Cronk For This Useful Post:
jaryszek (06-03-2019)
Old 06-04-2019, 12:11 AM   #8
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 608
Thanks: 300
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Many to many relationship but with exceptions

Hi Guys,

thank you for your help.

Colin -
Please read this:

https://stackoverflow.com/questions/...d-or-duplicate

Foreign key can be null but in Access maybe not?

Quote:
I faced a similar problem when I was asked by a large local government authority to set up a system to manage the issue of permits for organizations/companies and individuals wanting to use public land for events or commercial activities.


Either the permit was associated with the organization which in turn had an employee/member as the contact, or with an individual in their own right.


I stored two foreign keys in the permits table, OrganizationID and ContactID and created a "dummy" organization "Not Applicable". Selecting an Organization did filter the Contacts combo, except when N/A was chosen.
This is a good answer and i think the best here - i can use isntead of nullable fill TEXT string like "Not Applicable" or "NoMatch" or something like this. Later when query is used i can easy get proper results.

Quote:
That doesn't make sense. Normally ( and in this case) junction tables aren't real things, unlike other tables that represent real life entities, junction tables exist to sort out data between other real entity representing tables.

Your system version table contains no real unique data unto itself. It simple connects data from Topsysversiin to Operating systems. The only reason to have data in system version is to link data in the other two tables.

System version simple acts as a traffic cop directing data between this two other tables, it doesn't add any real data itself.
This is very important what you are writing here plog.
Can we go more into details? - i am not catching this.

I have SystemVersionOS - this is my junction table.


Best,
Jacek
Attached Images
File Type: png Screenshot_18.png (91.8 KB, 45 views)
jaryszek is offline   Reply With Quote
Old 06-04-2019, 05:54 PM   #9
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,418
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Many to many relationship but with exceptions

Ugh. When you want to discuss technical issues, especially with people not familiar with your technical details you can't casually reference items.

Your Initial post referenced "SystemVersions" in the text and had a tblSystemVersionOS in the image. Naturally I assumed they were the same thing. Now you have an image with tblSystemVersionOS as well as tblSystemVersion. I am now thoroughly confused. No idea what you mean when you use "SystemVersion" in your explanation.

Hopefully others do and can help.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
jaryszek (06-04-2019)
Old 06-04-2019, 08:09 PM   #10
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 608
Thanks: 300
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Many to many relationship but with exceptions

thank you plog,

no, other explanation is not needed. Cronk helped me!!!

Best,
Jacek

jaryszek is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
sentence case with exceptions bbwolff Modules & VBA 4 02-07-2014 05:15 AM
Exceptions List way2bord Modules & VBA 4 06-21-2013 02:07 PM
Relationship Exceptions milkman2500 Tables 1 05-28-2013 05:05 AM
Exceptions Queries robbydogg Queries 3 08-20-2008 11:55 PM
Field exceptions TKinzer Reports 0 10-24-2000 12:16 PM




All times are GMT -8. The time now is 04:29 PM.


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