Link to home
Start Free TrialLog in
Avatar of JDido
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|
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Your tables contains redundant values, that's what confuses you.
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
Avatar of JDido
JDido

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
Yes, that's how it works for a relational database.

/gustav
Avatar of JDido

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 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
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks.

/gustav
Avatar of JDido

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.
Avatar of JDido

ASKER

Thanks Gustav, it is now functioning as intended.
You are welcome!

/gustav