How to do a Hierarchical Query in Access?

[Docaroo]

Hi Guys,

I am designing a database for my girlfriend which uses a category -> sub-category -> item hierarchy for organisational purposes.

I have the following tables:

tblPhase
tblCategory
tblRisk

where Phase -> Category is one-to-many
and Category -> Risk is one-to-many

Risk table stores a Category Number and Category table stores a Phase Number. So each risk is hierarchically connected to a single Phase through the Category table...

Now, it's easy to find out all the risks in one category, but how can I query for all the risks in one phase (could be several categories) using SQL AND/OR VB?

Any help is much appreciated!

I've already googled and there's not much help on this subject tbh...

Thanks!

 

[Docaroo]

ok think I might have it...

I can build an expression in the risks query to select only categories from a given phase - now if I impliment this with VBA in my form I should have some good functionality

 

[Dolph]

It should just be a standard multiple table query.

SELECT tblphase.phasename, tblcategory.Category, tblrisk.Risk
FROM (tblphase INNER JOIN tblcategory ON tblphase.[ID] = tblcategory.[phase]) INNER JOIN tblrisk ON tblcategory.[ID] = tblrisk.[category]
WHERE (((tblphase.phasename)="name"));

Obviously the where clause would be where you define the criteria for selecting the phase.

Edit: looking at your last post, I think I've got the wrong end of the stick. Are you trying to populate form elements based on the previous selections? If so create lookup queries for each level (eg select category based on the selected phase) and requery on change on each box in VBA.

 

[Docaroo]

It should just be a standard multiple table query.

SELECT tblphase.phasename, tblcategory.Category, tblrisk.Risk
FROM (tblphase INNER JOIN tblcategory ON tblphase.[ID] = tblcategory.[phase]) INNER JOIN tblrisk ON tblcategory.[ID] = tblrisk.[category]
WHERE (((tblphase.phasename)="name"));

Obviously the where clause would be where you define the criteria for selecting the phase.

Edit: looking at your last post, I think I've got the wrong end of the stick. Are you trying to populate form elements based on the previous selections? If so create lookup queries for each level (eg select category based on the selected phase) and requery on change on each box in VBA.

Hi Dolph, thanks for the reply

I’ve actually managed to have the forms lookup without issue… I’ll explain in a bit more detail:

I have a combo box for Phase, Category and finally Risk – so that the user can drill down and see only specific risks – this then displays fields from the risk table (such as description, etc) in text boxes…

This is all fine and working, however I wanted to add some extra useability in that when you pick a phase, the RiskCombo actually shows all risks for that Phase (which could be several categories).

This is where I’m having problems. All the code is in place in that if no Phase is selected, all risks are displayed, and if a phase and category is selected – only those categories risks are chooseable.

However the problem is with the query that I can use to drill down the Phase and look at all it’s Categories and find their risks for the combo box.

From my investigation it seems Access doesn’t naturally support hierarchical querying which worried me! Will I have to hand code this query using VBA in order to manually look at the records??

Thanks!!

 

[Docaroo]

ahh brilliant!

I just tried that INNER JOIN query you wrote and that actually does exactly what I need! I just couldn't get my head around the JOINs in order to get my way!

This is how it looks with the proper names and columns:

SELECT Phase.[Phase Number], [Sub-Category].[Sub-Category Number], Risks.[Risk Number]
FROM (Phase INNER JOIN [Sub-Category] ON Phase.[Phase Number] = [Sub-Category].Phase) INNER JOIN Risks ON [Sub-Category].[Sub-Category Number] = Risks.Category
WHERE (((Phase.[Phase Number])=3));

Thanks a lot Dolph that's brilliant!!

Onwards!

 

[Dolph]

That is actually fairly simple to do. You can use VBA to change the row source query of the combo box (between a category list of risks and a phase list of risks as shown in my earlier code), and so all you need to do is decide how/in what circumstances you want to effect the change.

Depending on how the system is going to be used, I would either

• Set the row source on the risk box from the onchange event for the combo box for phase, then change it in the onchange event in the category box if a category is subsequently selected - This is the most transparent to the end user, but can be confusing, I'd suggest adding something to clear the category combo in the VBA for the phase combo to make it clear what's going on. It also potentially adds overhead if the user is switching phases regularly but only wants to check categories.
• Add a toggle box or similar to switch between phase level queries and category level queries. - Requires direct user choice, but only causes the script changing the target to fire when necessary

Access does not support anything similar to 'Start in... connect by...' in Oracle, but I'm not really aware of any of the other SQL languages that support similar. MSSQL doesn't even in SQL server.

Edit: I was writing this as you posted Glad it's sorted, happy to help.