Hierarchial query

[Mellout]

I have one table SCTHIER which contains 4 columns (PARENT, CHILD, PARENT_DESCRIPTION, CHILD_DESCRIPTION).

I would like to query 1 code i.e. PARENT code = 63102001, and get ALL child/sub codes/sub-sub codes/sub-sub-sub codes in just 1 column

So for example in the table below:


63102001 has child/sub codes 105597003, 246636008, 193683001
of which 105597003 has child/sub codes 313165001, 361122008 etc

PARENT	SCTHIER.CHILD	SCTHIER_1.CHILD	SCTHIER_2.CHILD	SCTHIER_3.CHILD	SCTHIER_4.CHILD	SCTHIER_5.CHILD
63102001	105597003	313165001
63102001	105597003	361122008	68777001	232150003	193722001
63102001	105597003	361122008	68777001	75133003
63102001	105597003	361122008	68777001
63102001	246636008	12342234
63102001	246636008
63102001	193683001	232148006	56852002	789675009
63102001	193683001	232148006	77479002
63102001	193683001	232148006	789676005
63102001	193683001	260030003

I would want the output to look something like this:
63102001
105597003
246636008
193683001
313165001
361122008
12342234
232148006
260030003
68777001
56852002
77479002
789676005
232150003
75133003
789675009
193722001

Please bear in mind I'm quite new to Access, and may not know some of the complex areas.

Many Thanks

[orange]

Can you tell/show us a few details about your table with PARENT, CHILD, PARENT_DESCRIPTION, CHILD_DESCRIPTION?
How does that table structure and your displayed data values relate?
A brief description of the business this application supports would be helpful.

[Welshgasman]

I can see the output from the data, bu t I am going to go out on a limb here and say your DB is all wrong and not normalized.?
I would go for a table that held a parent and child field, so the parent field would hold the ID of the parent record.

As always, my first stop would be Google, even when I have an idea of what it should be like

https://www.google.com/search?q=hier...client=gws-wiz

[Micron]

Agree re: it looks like lack of normalization is an issue - table is designed like a spreadsheet. A UNION query might get you what you want.

[CJ_London]

What you are trying to do is called a recursive process. In access that involves using a vba function that repeatedly calls itself as it iterates through the data - google something like 'access vba recursive function' to find out more.

I agree with the others, your table design needs modifying to something like

PK...........Description...FK
63102001....A........................Null (null means this is the top parent)
105597003..B........................63102001
246636008..C........................63102001
193683001..D........................63102001
313165001..E.......................105597003
361122008..F........................105597003
68777001....G.......................361122008
etc

You did say

I have one table SCTHIER which contains 4 columns (PARENT, CHILD, PARENT_DESCRIPTION, CHILD_DESCRIPTION).

which if it is like the above, you just need to drop the child description since it will be repeated in the child record

But that doesn't match the table you provided.....

[Mellout]

Thank you everyone for your responses. The initial table is a download from a national website containing over 1 million codes. Sample below:

PARENT	PTERM	CHILD	CTERM
63102001	Visual disturbance	105597003	Blindness AND/OR vision impairment level
63102001	Visual disturbance	193683001	Colour blindness
63102001	Visual disturbance	246636008	Hazy vision
105597003	Blindness AND/OR vision impairment level	313165001	Functional visual loss
105597003	Blindness AND/OR vision impairment level	361122008	Impairment level of vision
193683001	Colour blindness	232148006	Congenital colour blindness
193683001	Colour blindness	260030003	Daltonism
246636008	Hazy vision	12342234	Test
246636008	Hazy vision
232148006	Congenital colour blindness	56852002	Achromatopsia
232148006	Congenital colour blindness	77479002	Deutan defect
232148006	Congenital colour blindness	789676005	Blue cone monochromatism
361122008	Impairment level of vision	68777001	Impairment level of both eyes
56852002	Achromatopsia	789675009	Complete achromatopsia

It's really helpful to understand it's recursive, but is there anything I can do using the existing table?

Please note 260030003 is only in the child field, and not in parent field.

Thank you

[CJ_London]

Please note 260030003 is only in the child field, and not in parent field.

so how do you know the child has children?

[Mellout]

If the child code also appears in the parent field.

So for example I want to return all the codes below 63102001. Child code of 63102001, sub-child codes of those codes etc.It goes down about 5-6 levels.

[CJ_London]

can you confirm that - it is not what you said originally.

But assuming that is the case then create a new table using a union query then Microns suggestion of a union query will get you to where you need to be for a recursive action

Given the volume of data, before you start, compact the db with the original data and check file size - if it is over 1Gb then suggest create an new db and link to this table so your new table is in a different database to avoid the risk of hitting the 2Gb limit.

SELECT PARENT AS ParentPK, PTERM, Null as ParentFK FROM myTable
UNION SELECT CHILD, CTERM, PARENT FROM myTable

Note this will get rid of duplicate records

then create a maketable query based on this and once populated index ParentPK as Primary Key and ParentFK as indexed, duplicates OK.

Reason for indexing after creating the records is with 1m rows, it will be faster.

It does occur to me that with 1m+ records a recursive function will be slow unless you are only looking at one parent. If you know the maximum number of levels then you could write a query using left joins between the table and itself for than number of records which should be quicker. Something like

SELECT A.ParentPK AS F1, B.ParentPK AS F2, C.ParentPK AS F3, D.ParentPK etc
((myTable A left join myTable B ON A.parentPK=B.parentFK)
LEFT JOIN myTable C ON B

.parentPK=C.parentFK)
LEFT JOIN myTable D ON C.parentPK=D.parentFK
etc
WHERE A.ParentPK=63102001

Edit - for some reason the forum has split the code into two code blocks, not sure why

[Mellout]

Thank you. I may have confused matters by inserting the wrong table into my first query. The first table was based on LEFT JOIN as you mentioned, however all the data comes out in multiple columns rather than just one column.

SELECT SCTHIER.PARENT, SCTHIER.CHILD, SCTHIER_1.CHILD, SCTHIER_2.CHILD, SCTHIER_3.CHILD, SCTHIER_4.CHILD, SCTHIER_5.CHILD
FROM (((((SCTHIER
LEFT JOIN SCTHIER AS SCTHIER_1 ON SCTHIER.CHILD = SCTHIER_1.PARENT)
LEFT JOIN SCTHIER AS SCTHIER_2 ON SCTHIER_1.CHILD = SCTHIER_2.PARENT)
LEFT JOIN SCTHIER AS SCTHIER_3 ON SCTHIER_2.CHILD = SCTHIER_3.PARENT)
LEFT JOIN SCTHIER AS SCTHIER_4 ON SCTHIER_3.CHILD = SCTHIER_4.PARENT)
LEFT JOIN SCTHIER AS SCTHIER_5 ON SCTHIER_4.CHILD = SCTHIER_5.PARENT

WHERE (((SCTHIER.PARENT)="63102001"));

I'm afraid I'm lost on the next steps for recursion though, but really appreciate everyone's help.

[CJ_London]

If you don't want to get into recursion the use a union query on your left join query I'll call it qryLJ and changed the SELECT part to

SELECT SCTHIER.PARENT, SCTHIER.CHILD AS child0, SCTHIER_1.CHILD AS child1, SCTHIER_2.CHILD AS child2, SCTHIER_3.CHILD AS child3, SCTHIER_4.CHILD AS child4, SCTHIER_5.CHILD AS child5

as it makes the next step easier

SELECT PARENT FROM qryLJ
UNION SELECT CHILD0 FROM qryLJ
UNION SELECT CHILD1 FROM qryLJ
UNION SELECT CHILD2 FROM qryLJ
UNION SELECT CHILD3 FROM qryLJ
UNION SELECT CHILD4 FROM qryLJ
UNION SELECT CHILD5 FROM qryLJ

[Mellout]

Thank you, that's great. I managed to use this principle and get what I needed. Thank you so much .

[CJ_London]

if you want to know how much a relationship is moved from the parent, you can add it into the union query

SELECT PARENT, 0 as removed FROM qryLJ
UNION SELECT CHILD0,1 FROM qryLJ
UNION SELECT CHILD1,2 FROM qryLJ
UNION SELECT CHILD2,3 FROM qryLJ
UNION SELECT CHILD3,4 FROM qryLJ
UNION SELECT CHILD4,5 FROM qryLJ
UNION SELECT CHILD5,6 FROM qryLJ