Derive relationships based on data

jex57

Board Regular
Joined
Oct 29, 2015
Messages
62
Hi all,

I have a database - about databases - so what it does is stores all information about the various databases that we run, table names, columns that are linked column sizes, descriptions etc.

I have a System table that links to a MasterTable (table) and then a column table. The column table stores a foreign key and a primary key indicator.

Is there a way to derive relationships by reading through the MasterTable, picking up the Primary key indicator from the Column table, and then checking for foreign keys in other columns that belong to the same system.

I have code that can determine relationships between actual tables in MS Access, but I need to determine the relationships based on the data that is stored.

Thank you
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
If the foreign key and primary key have the same name you can use a self join on the table, see here:

http://allenbrowne.com/ser-06.html


This works if you are looking for table relationships. I am looking for a way to determine relationships from the date.

For example

SystemTable_data
SystemA
SystemB
SystemC

MasterTable_data
SystemA Table 1
SystemA Table 2
SystemA Table 3
SystemB Table 4


Column_data
Table 1 Column A PK
Table 1 Column A FK
Table 1 Column A
Table 1 Column A

Table 2 Column A PK
Table 2 Column A
Table 2 Column A
Table 2 Column A


Table_results - this is the table that I would need to generate
System Table RelationshipType
SystemA Table 1 Child to Table 2
SystemA Table 2 Parent to Table 1


Not sure if this makes sense
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top