JDido
asked on
Setting up a cascading Level relationship
Having trouble setting up relationships in Access. Have a simple database that s based 3 levels. Each level cascades.
First table is just a list of Level1 categories.
Second table uses a lookup to first table and has different Level2 categories for each Level 1. This setup no problem.
My problem is the third table. I want tblLIST_Level3 |Level2| to only allow values of |Level2| that correspond to the values for that|Level1| in tblLIST_Level2.
tblLIST_Level1
ID|Level1|
tblLIST_Level2
ID|Level1|Level2|
tblLIST_Level3
ID|Level1|Level2|Level3|
First table is just a list of Level1 categories.
Second table uses a lookup to first table and has different Level2 categories for each Level 1. This setup no problem.
My problem is the third table. I want tblLIST_Level3 |Level2| to only allow values of |Level2| that correspond to the values for that|Level1| in tblLIST_Level2.
tblLIST_Level1
ID|Level1|
tblLIST_Level2
ID|Level1|Level2|
tblLIST_Level3
ID|Level1|Level2|Level3|
ASKER
Okay maybe I'm going about this in the wrong way (table structure wise)... I'll start with what I want at the end and maybe you can suggest a different structure.
I want a table that stores records based on users selection for Level 1, Level 2, Level 3.
Level 3 options are unique to the selection in Level 2. Level 2 options are unique to the selection in Level 1.
Example from attached picture:
If user selects Level 1 = A, they only get C,D,E as options for Level2. If they then select E for Level 2 they only get M,N as options for Level 3.
2014-05-06-192827.png
I want a table that stores records based on users selection for Level 1, Level 2, Level 3.
Level 3 options are unique to the selection in Level 2. Level 2 options are unique to the selection in Level 1.
Example from attached picture:
If user selects Level 1 = A, they only get C,D,E as options for Level2. If they then select E for Level 2 they only get M,N as options for Level 3.
2014-05-06-192827.png
Yes, that's how it works for a relational database.
/gustav
/gustav
ASKER
I did that but it still gives all the records for Field3 regardless of Field2; likewise all for Field2 regardless of Field1.
I was hoping the relationships would hold even for direct entry into a table rather than putting artificial restrictions on a form entry.
To do it the latter way, I've seen elsewhere that I may need to have an SQL statement in Row Source on the forms that have comboboxes for Field 1, 2,3?
I tried that.
Combobox for Field1:
SELECT [tblLIST_Level1].[ID], [tblLIST_Level1].[Level1] FROM tblLIST_Level1;
OK So far.
Combobox for Field2:
SELECT [tblLIST_Level2].[ID], [tblLIST_Level2].[Level2] FROM tblLIST_Level2 WHERE [tblLIST_Level2].[Level1ID ] = Forms!frmDATA_UI!Level1;
Only gives Level2 options for the first Level1 record regardless of which is selected in Field1 Combobox.
Combobox fo Field3:
SELECT [tblLIST_Level3].[ID], [tblLIST_Level3].[Level3] FROM tblLIST_Level3 WHERE [tblLIST_Level3].[Level2ID ] = Forms!frmDATA_UI!Level2;
Gives no options a blank list.
I was hoping the relationships would hold even for direct entry into a table rather than putting artificial restrictions on a form entry.
To do it the latter way, I've seen elsewhere that I may need to have an SQL statement in Row Source on the forms that have comboboxes for Field 1, 2,3?
I tried that.
Combobox for Field1:
SELECT [tblLIST_Level1].[ID], [tblLIST_Level1].[Level1] FROM tblLIST_Level1;
OK So far.
Combobox for Field2:
SELECT [tblLIST_Level2].[ID], [tblLIST_Level2].[Level2] FROM tblLIST_Level2 WHERE [tblLIST_Level2].[Level1ID
Only gives Level2 options for the first Level1 record regardless of which is selected in Field1 Combobox.
Combobox fo Field3:
SELECT [tblLIST_Level3].[ID], [tblLIST_Level3].[Level3] FROM tblLIST_Level3 WHERE [tblLIST_Level3].[Level2ID
Gives no options a blank list.
I guess Forms!frmDATA_UI!Level1 and -2 are the comboboxes.
In the AfterUpdate event of these, requery the combox(es) below:
Me!Level2.Requery
Me!Level3.Requery
/gustav
In the AfterUpdate event of these, requery the combox(es) below:
Me!Level2.Requery
Me!Level3.Requery
/gustav
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks.
/gustav
/gustav
ASKER
Thanks for all the help Gustav and the extra comments Dale.
I'm not ignoring you, I've just been pulled away onto other things today. I will get back to looking at this tomorrow. I appreciate the help and will close this out (I hope) tomorrow.
I'm not ignoring you, I've just been pulled away onto other things today. I will get back to looking at this tomorrow. I appreciate the help and will close this out (I hope) tomorrow.
ASKER
Thanks Gustav, it is now functioning as intended.
You are welcome!
/gustav
/gustav
Only carry forward (or down) the ID of the parent table:
tblLIST_Level1
ID|Level1|
tblLIST_Level2
ID|Level1ID|Level2|
tblLIST_Level3
ID|Level2ID|Level3|
Thus, relations will be:
tblLIST_Level1.ID - tblLIST_Level2.Level1ID
tblLIST_Level2.ID - tblLIST_Level3.Level2ID
/gustav