HELP!

[Stacy]

Hey everyone! I have an issue I can't seem to get sorted out on my own here
that I've been trying to find an answer to for a week now. Here's what I've
got:

I have the following tables:
1) Menus
2) Responsibilities

On the Menus table, there are the following columns:

Menu ID
Menu Name
SubMenu ID

On the Responsibilities table I've got the following columns:

Responsibility Key
Responsibility Name
Responsibility ID
Menu ID

The problem exists in that every single "Menu" available to us shows up in
the Menu ID column somewhere. Because of the way we structured Menus I also
have to look at the Sub-Menu field. The value in the submenu field is going
to show up somewhere in the MenuID column of the same table. The potential
is there where the submenu may have another submenu, so on and so forth. See
below for a possible better example:

Menu ID Menu Name SubMenuID
----------- ---------------- ----------------
12345 ABC Menu 78910
67891 DEF Menu 01254
11121 GHI Menu
78910 JKL Menu 11121
01254 MNO Menu 12345

As you will see, we are effectively criss-crossed all over the place.
Unforunately, there is not much I can do to control that aspect, as this is a
data dump out of another application I'm trying to sort out.

Using the scenario above, I want to be able to run a query in access that
will show me the following:

responsibility key, name, id and menu ID from the responsibility table
menu ID and all associated submenus from the menu table and criss-cross
pattern above.

Any thoughts? Help? Please?

 

[Michel Walsh]

HI,


If there is a maximum level of sub sub sub menu, write a LEFT JOIN for each
level. For ONE level, that is:

SELECT a.*, b.*
FROM menus AS a LEFT JOIN menus AS b
ON a.submenuID = b.menuID



to get:


a.Menu ID a. Menu Name a.SubMenuID b.MenuID
b.MenuName b.SubMenuID
----------- ---------------- ----------------
12345 ABC Menu 78910 78910
JKL Menu 11121
67891 DEF Menu 01254 01254
MNO Menu 12345
11121 GHI
--- --- --- ---
78910 JKL Menu 11121 11121
GHI Menu ---
01254 MNO Menu 12345 12345
ABC Menu 78910


Just repeat the process for another level:

SELECT a.*, b.*, c.*
FROM (menus AS a LEFT JOIN menus AS b
ON a.submenuID = b.menuID) LEFT JOIN menus AS c
ON b.submenuID = c.menuID



etc, ad nauseam.

Sure, you cannot dynamically type a variable number of LEFT JOIN, so, you
would be limited to a predefined number of sub level.

To get real recursion (dynamical number of sub level), use either MS SQL
Server 2005 new construction at that effect, either use a 'nested set'
approach.



Hoping it may help,
Vanderghast, Access MVP