Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-11-2019, 05:16 AM   #1
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 507
Thanks: 227
Thanked 0 Times in 0 Posts
jaryszek is on a distinguished road
Tables restriction

hi Guys,

i have relationships like here:



full description of model you can find here:

https://www.access-programmers.co.uk...d.php?t=304008

I have to change a little approach and need your help.

And now i have 2 tables: restrictedTable1 and restrictedTable2.
In each table you can see for which systemversion and topology i should restrict another systemversion for specific topology.

I created query to show what i want to:



so as you can see there are TopologyName and specific systemVersions for each of them.

And now each systemVersion specific for TopologyName should restrict usage of systemVersion characteristic for specififc Topology Name.
So systemVersion can restrict other SystemVersions based on restrictedTables dependencies.
Example:
In RestrictedTable2 there is topologyName FE in first column which should restrict Topology NW with specific SystemVersions,
So "SAP S/4HANA 1610 SP 01" for FE will restrict NW SystemVersions from table tblSystemVersion for only one choice: "SAP S/4HANA 1610 SP 01".

How can i appropriete restrict table tblSystemVersions using specific Topology Names and System Versions?

I thought to create one new table where i would put all dependencies.
But i do not know how to solve this technically.

Please help,
Best Wishes,
Jacek
Attached Images
File Type: jpg Screenshot_5.jpg (59.1 KB, 147 views)
File Type: png Screenshot_7.png (76.5 KB, 143 views)
Attached Files
File Type: accdb Database1611.accdb (572.0 KB, 6 views)

jaryszek is offline   Reply With Quote
Old 03-11-2019, 05:57 AM   #2
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,546
Thanks: 25
Thanked 466 Times in 443 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Tables restriction

Those restrictions tables do not look normalized. You have version names as columns. Why can you not simply have a single table?

TblAvailableVersions
Code:
  SystemVersionOne_FK
  SystemVersionTwo_Fk

For each SystemVersion1 you would list every possible SystemVersionTwo for each Topology. Since a system version has a topology as a related field. Once this table is complete you can check a topology and see the available system versions for each topology.
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
jaryszek (03-11-2019)
Old 03-11-2019, 08:19 AM   #3
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 507
Thanks: 227
Thanked 0 Times in 0 Posts
jaryszek is on a distinguished road
Re: Tables restriction

hi,

thank you!
Let me understand this.
I created new table TblAvailableVersions with these fields.
How can i relate them with present tables?

Quote:
Those restrictions tables do not look normalized. You have version names as columns.
agree, i can transform these tables to something like:



Quote:
For each SystemVersion1 you would list every possible SystemVersionTwo for each Topology.
I do not catch it yet, how can i add into tblAvailableVersion and how can i relete this table into present model?

Please give more details,
idea is awesome!

Best wishes,
Jacek
Attached Images
File Type: png Screenshot_8.png (88.3 KB, 113 views)
Attached Files
File Type: accdb Database1611.accdb (600.0 KB, 4 views)

jaryszek is offline   Reply With Quote
Old 03-11-2019, 09:04 AM   #4
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 507
Thanks: 227
Thanked 0 Times in 0 Posts
jaryszek is on a distinguished road
Re: Tables restriction

Ok, i deleted Topology Field from tblSystemVersions table because we have topologies already in separate table tblTopology and with many to many relationship for customerTopology table.

Sorry for confusion,
Best,
Jacek
Attached Files
File Type: accdb Database1611.accdb (596.0 KB, 4 views)
jaryszek is offline   Reply With Quote
Old 03-11-2019, 09:05 AM   #5
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,546
Thanks: 25
Thanked 466 Times in 443 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Tables restriction

You are not using your PKs as you should. In my mind based on your descriptions the available version table is simply for each SV the related SVs in each topology.

Code:
SystemVersionOne_FK	SystemVersionTwo_FK
14	1
14	2
14	3
14	4
14	5
14	6
14	7
14	8
14	11
14	12
15	1
15	2
15	3
15	4
15	5
15	6
15	7
15	8
15	9
15	11
15	12
16	1
16	2
16	3
16	4
16	5
16	6
16	7
16	8
16	9
16	10
16	11
16	12
16	13
2	11
4	12
10	13
11	2
12	4
13	10


Since the topology ID is in the system version relationship I can bring this all together in a query as needed.
Code:
SELECT SystemVersionOne.topology, 
       SystemVersionOne.systemversion, 
       tblavailableversions.systemversionone_fk, 
       SystemVersionTwo.topology, 
       SystemVersionTwo.systemversion, 
       tblavailableversions.systemversiontwo_fk 
FROM   (tblsystemversions AS SystemVersionOne 
        INNER JOIN tblavailableversions 
                ON SystemVersionOne.systemversionid = 
                   tblavailableversions.systemversionone_fk) 
       INNER JOIN tblsystemversions AS SystemVersionTwo 
               ON tblavailableversions.systemversiontwo_fk = 
                  SystemVersionTwo.systemversionid;

Code:
SystemVersionOne.Topology	SystemVersionOne.SystemVersion	SystemVersionOne_FK	SystemVersionTwo.Topology	SystemVersionTwo.SystemVersion	SystemVersionTwo_FK
HANA	HANA 1.0 SPS12	14	NW	Netweaver 7.5 SP 01	1
HANA	HANA 1.0 SPS12	14	NW	SAP S/4HANA 1610 SP 01	2
HANA	HANA 1.0 SPS12	14	NW	SAP CRM 7.0 EHP4	3
HANA	HANA 1.0 SPS12	14	NW	SAP S/4HANA 1709	4
HANA	HANA 1.0 SPS12	14	NW	SAP ERP 6.0 EHP8	5
HANA	HANA 1.0 SPS12	14	NW	SAP SCM 7.0 EHP4	6
HANA	HANA 1.0 SPS12	14	NW	SAP SRM 7.0 EHP4	7
HANA	HANA 1.0 SPS12	14	NW	Netweaver 7.5 SP 13	8
HANA	HANA 1.0 SPS12	14	FE	SAP S/4HANA 1610 SP 01	11
HANA	HANA 1.0 SPS12	14	FE	SAP S/4HANA 1709	12
HANA	HANA 2.0 SPS2	15	NW	Netweaver 7.5 SP 01	1
HANA	HANA 2.0 SPS2	15	NW	SAP S/4HANA 1610 SP 01	2
HANA	HANA 2.0 SPS2	15	NW	SAP CRM 7.0 EHP4	3
HANA	HANA 2.0 SPS2	15	NW	SAP S/4HANA 1709	4
HANA	HANA 2.0 SPS2	15	NW	SAP ERP 6.0 EHP8	5
HANA	HANA 2.0 SPS2	15	NW	SAP SCM 7.0 EHP4	6
HANA	HANA 2.0 SPS2	15	NW	SAP SRM 7.0 EHP4	7
HANA	HANA 2.0 SPS2	15	NW	Netweaver 7.5 SP 13	8
HANA	HANA 2.0 SPS2	15	NW	Netweaver 7.52	9
HANA	HANA 2.0 SPS2	15	FE	SAP S/4HANA 1610 SP 01	11
HANA	HANA 2.0 SPS2	15	FE	SAP S/4HANA 1709	12
HANA	HANA 2.0 SPS3	16	NW	Netweaver 7.5 SP 01	1
HANA	HANA 2.0 SPS3	16	NW	SAP S/4HANA 1610 SP 01	2
HANA	HANA 2.0 SPS3	16	NW	SAP CRM 7.0 EHP4	3
HANA	HANA 2.0 SPS3	16	NW	SAP S/4HANA 1709	4
HANA	HANA 2.0 SPS3	16	NW	SAP ERP 6.0 EHP8	5
HANA	HANA 2.0 SPS3	16	NW	SAP SCM 7.0 EHP4	6
HANA	HANA 2.0 SPS3	16	NW	SAP SRM 7.0 EHP4	7
HANA	HANA 2.0 SPS3	16	NW	Netweaver 7.5 SP 13	8
HANA	HANA 2.0 SPS3	16	NW	Netweaver 7.52	9
HANA	HANA 2.0 SPS3	16	NW	SAP S/4HANA 1809	10
HANA	HANA 2.0 SPS3	16	FE	SAP S/4HANA 1610 SP 01	11
HANA	HANA 2.0 SPS3	16	FE	SAP S/4HANA 1709	12
HANA	HANA 2.0 SPS3	16	FE	SAP S/4HANA 1809	13
NW	SAP S/4HANA 1610 SP 01	2	FE	SAP S/4HANA 1610 SP 01	11
NW	SAP S/4HANA 1709	4	FE	SAP S/4HANA 1709	12
NW	SAP S/4HANA 1809	10	FE	SAP S/4HANA 1809	13
FE	SAP S/4HANA 1610 SP 01	11	NW	SAP S/4HANA 1610 SP 01	2
FE	SAP S/4HANA 1709	12	NW	SAP S/4HANA 1709	4
FE	SAP S/4HANA 1809	13	NW	SAP S/4HANA 1809	10
So with table tblAvailableVersions you would have all restrictions.
MajP is offline   Reply With Quote
Old 03-11-2019, 09:20 AM   #6
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,546
Thanks: 25
Thanked 466 Times in 443 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Tables restriction

Code:
i deleted Topology Field from tblSystemVersions
I posted before seeing this. So you cannot pull the Topology in a query. Your tblAvailableVersions should then contain the topology

tblAvailableVersion
TopologyOneID_FK
SystemVersionOne_FK
TopologyTwoID_FK
SystemVersionTwo_FK

your tblAvailableVerions looks like

Code:
TopologyOne_FK	SystemVersionOne_FK	TopologyTwo_FK	SystemVersionTwo_FK
1	14	1	1
1	14	1	2
1	14	1	3
1	14	1	4
1	14	1	5
1	14	1	6
1	14	1	7
1	14	1	8
1	14	1	11
1	14	1	12
1	15	1	1
1	15	1	2
1	15	1	3
1	15	1	4
1	15	1	5
1	15	1	6
1	15	1	7
1	15	1	8
1	15	1	9
1	15	1	11
1	15	1	12
1	16	1	1
1	16	1	2
1	16	1	3
1	16	1	4
1	16	1	5
1	16	1	6
1	16	1	7
1	16	1	8
1	16	1	9
1	16	1	10
1	16	1	11
1	16	1	12
1	16	1	13
2	11	2	2
2	12	2	4
2	13	2	10
3	2	3	11
3	4	3	12
3	10	3	13
this shows all restrictions for all topology. You may need to duplicate the records because I assume the restrictions go both ways. Depending on what topology is selected first. (This may or may not be the case)

In other words if you have
HANA HANA 1.0 SPS12 14 NW Netweaver 7.5 SP 01 1
you may want the record
NW Netweaver 7.5 SP 01 1 HANA HANA 1.0 SPS12
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
jaryszek (03-11-2019)
Old 03-11-2019, 10:20 PM   #7
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 507
Thanks: 227
Thanked 0 Times in 0 Posts
jaryszek is on a distinguished road
Re: Tables restriction

Hi MajP,

thank you! Awesome explanation! So the tblAvailableVersion you would create as separate, not related table?

And when i want to use values i can simple query and get whatever i want.

Best,
Jacek

jaryszek is offline   Reply With Quote
Old 03-12-2019, 02:52 AM   #8
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 507
Thanks: 227
Thanked 0 Times in 0 Posts
jaryszek is on a distinguished road
Re: Tables restriction

Ok,

i created separate table and now i am trying to transform imported restrcitedtables from textes to numbers as foreign keys.



So i need the process of matching string fields with appropriate numbers.




How to combine this appropriately?
I tried do the same for Topology2 and VersionName2 but after joining queries my results were multiplied...

Please help,
Best wishes,
Jacek
Attached Images
File Type: jpg Screenshot_10.jpg (88.4 KB, 74 views)
File Type: png Screenshot_11.png (95.5 KB, 73 views)
File Type: png Screenshot_12.png (64.5 KB, 74 views)
Attached Files
File Type: accdb Database1611.accdb (612.0 KB, 5 views)
jaryszek is offline   Reply With Quote
Old 03-12-2019, 11:24 AM   #9
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,546
Thanks: 25
Thanked 466 Times in 443 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Tables restriction

Sorry I made a mistake in the table posted. Where it lists topologyTwo_FK it shows 1 where it should show 3 (NW). Using your two restriction tables I would get the following.

TblAvailableVersions
Code:
TopologyOne_FK	SystemVersionOne_FK	TopologyTwo_FK	SystemVersionTwo_FK
1	14	3	1
1	14	3	3
1	14	3	4
1	14	3	5
1	14	3	6
1	14	3	8
1	14	3	11
1	15	3	1
1	15	3	3
1	15	3	4
1	15	3	5
1	15	3	6
1	15	3	8
1	15	3	9
1	15	3	11
1	16	3	1
1	16	3	3
1	16	3	4
1	16	3	5
1	16	3	6
1	16	3	8
1	16	3	9
1	16	3	10
1	16	3	11
2	4	3	4
2	10	3	10
2	11	3	11
Now this first block shows what NW versions are available when you have Hana topology and HANA 1.0 SPS12.

I will assume you need the reflection of this. So if you have NW topology to start and you want to know the Hana versions. If that is the case you need the reflection. Your table would then look like

Code:
TopologyOne_FK	SystemVersionOne_FK	TopologyTwo_FK	SystemVersionTwo_FK
1	14	3	1
1	14	3	3
1	14	3	4
1	14	3	5
1	14	3	6
1	14	3	8
1	14	3	11
1	15	3	1
1	15	3	3
1	15	3	4
1	15	3	5
1	15	3	6
1	15	3	8
1	15	3	9
1	15	3	11
1	16	3	1
1	16	3	3
1	16	3	4
1	16	3	5
1	16	3	6
1	16	3	8
1	16	3	9
1	16	3	10
1	16	3	11
2	4	3	4
2	10	3	10
2	11	3	11
3	1	1	14
3	3	1	14
3	4	1	14
3	5	1	14
3	6	1	14
3	8	1	14
3	11	1	14
3	1	1	15
3	3	1	15
3	4	1	15
3	5	1	15
3	6	1	15
3	8	1	15
3	9	1	15
3	11	1	15
3	1	1	16
3	3	1	16
3	4	1	16
3	5	1	16
3	6	1	16
3	8	1	16
3	9	1	16
3	10	1	16
3	11	1	16
3	4	2	4
3	10	2	10
3	11	2	11
You do want to make a composite index on this table where you cannot duplicate a record with the same 4 fields. To see all the system topology data you just build a query by adding the topology table and the system table in twice. You can make a relation to enforce referential integrity, but it would be its own "island" showing the relations to the topology and system tables, but not connected to your other relationships.
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
jaryszek (03-12-2019)
Old 03-13-2019, 12:07 AM   #10
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 507
Thanks: 227
Thanked 0 Times in 0 Posts
jaryszek is on a distinguished road
Re: Tables restriction

hi MajP,

thank you, i will
how can i create relation here? i do not have even key which i can combined with this own island

Best,
Jacek
jaryszek is offline   Reply With Quote
Old 03-13-2019, 03:55 AM   #11
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,546
Thanks: 25
Thanked 466 Times in 443 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Tables restriction

Quote:
how can i create relation here? i do not have even key which i can combined with this own island
Take tblAvailableVersions and put in the middle. Add two copies of tblTopology one on the left side and one on the right side. Add two copies of tblSystem versions one on the left and one on the right. Connect left side tables to topologyOne_Fk, and systemVersionOne_FK. The right side connect to topolgytwo and systemTwo. You can then enforce referential integrity so that you do not get values in tblAvailableVersions that do not exist in the primary tables.
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
jaryszek (03-13-2019)
Old 03-13-2019, 04:22 AM   #12
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 507
Thanks: 227
Thanked 0 Times in 0 Posts
jaryszek is on a distinguished road
Re: Tables restriction

oo perfect, thank you very much. i am learning every day something new

Best,
Jacek
jaryszek is offline   Reply With Quote
Old 03-15-2019, 01:15 AM   #13
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 507
Thanks: 227
Thanked 0 Times in 0 Posts
jaryszek is on a distinguished road
Re: Tables restriction

Hi,

i have one more question regarding this:

how should i create index in tblAvailableVersions?
I should have PK and separate index called (i do not know what name conventions should i use for indexing in Access) IndexComposite for 4 separate fields?

Best,
Jacek
jaryszek is offline   Reply With Quote
Old 03-15-2019, 03:30 AM   #14
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,546
Thanks: 25
Thanked 466 Times in 443 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Tables restriction

How to create a composite index that is not the PK
http://www.geeksengine.com/article/c...ex-access.html
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
jaryszek (03-15-2019)
Old 03-15-2019, 04:42 AM   #15
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 507
Thanks: 227
Thanked 0 Times in 0 Posts
jaryszek is on a distinguished road
Re: Tables restriction

thank you !

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
Combo box Restriction Bigthinkor General 2 01-15-2011 04:58 PM
Printing restriction krzysiekk Reports 1 09-22-2010 11:18 AM
Combo Box Restriction Stemdriller Forms 2 06-14-2010 12:26 AM
Date restriction. yeppy12 Forms 6 10-23-2007 10:36 PM
Access restriction Housewares General 6 03-24-2004 10:11 AM




All times are GMT -8. The time now is 01:05 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