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

 

[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!