Access: how to query until final parent is found?

Question

I have a composition of assembly units described in table tblComposition like this:

+-------+--------+----------+
| Child | Parent | Quantity |
+-------+--------+----------+
|   111 |     11 |        1 |
|   112 |     11 |        4 |
|   113 |     11 |        1 |
|   211 |     21 |        3 |
|   222 |     22 |        5 |
|    21 |      2 |        1 |
|    22 |      2 |        3 |
|    11 |      1 |        1 |
|    12 |      1 |        1 |
|    1  |      1 |        1 |
|    2  |      2 |        1 |
+-------+--------+----------+

That means that 5 parts number 222 is assembled in unit 22, and then 3 units 22 are is assembled in product 2. The nesting can be arbitrarily large.

If I define then production plan for next day in tblProducts:

+---------+------+
| Product | Plan |
+---------+------+
|       1 |  123 |
|       2 |  456 |
+---------+------+

Is there any SQL query or VBA code to acheve automatic calculation of plan for manufacturing child subassemblies and parts? So I could have the following result:

+-------+----------+
| Child | Plan     |
+-------+----------+
|   111 |      123 |
|   112 |      492 |
|   113 |      123 |
|   211 |     1368 |
|   222 |     6840 |
|    21 |      456 |
|    22 |     1368 |
|    11 |      123 |
|    12 |      123 |
|    1  |      123 |
|    2  |      456 |
+-------+----------+

Does parent 1 occur in the table as a child with itself as a parent? — Harassed Dad, Mar 03 '20 at 10:39
112 is also child of 11 but not affected. Are you applying this `nesting` only when quantity is 1? please add all your conditons to your question and use unified terminology. — Krish, Mar 03 '20 at 10:56
@HarassedDad, yes, why not I have edited initial and result table — Triostrong, Mar 03 '20 at 11:34
@krishKM, I'm not sure, what do you mean.112 is a child of 11 and has plan of 492 = 123 (plan of 11) * 4pcs (pieces per product). — Triostrong, Mar 03 '20 at 11:38
@Triostrong . . . MS Access has no support for recursive or hierarchical queries. You need to do this using application code, presumably VBA. — Gordon Linoff, Mar 03 '20 at 12:00
@Triostrong: krish KM means that Parent 11 has in your 3rd table 2 different plans - 123 and 492. The same with parent 1 and 2. This is not consistent. You probably need to clarify that before we can help you. — Vlado, Mar 03 '20 at 12:02
@Vlado, sorry for misinforming, in the third table second column was just a reference and I have deleted it in latest edit. — Triostrong, Mar 03 '20 at 13:28
@GordonLinoff, thank you, I had no idea about it. Looks like [this question](https://stackoverflow.com/questions/763016/is-it-possible-to-create-a-recursive-query-in-access) covers my problem. — Triostrong, Mar 03 '20 at 13:29
Does this answer your question? [Is it possible to create a recursive query in Access?](https://stackoverflow.com/questions/763016/is-it-possible-to-create-a-recursive-query-in-access) — Krish, Mar 04 '20 at 11:25

score 0 · Answer 1 · answered Mar 03 '20 at 11:58

0

I have done it using the Postgres syntax, because I don't have Access and I don't know the exact syntax.

WITH RECURSIVE composition(Child,Parent,Quantity) AS (
    SELECT tbc.Child,tbc.Parent,tp.Plan
    FROM tblComposition tbc, tblProducts tp 
    WHERE tbc.Parent = tp.Product and tbc.child = tbc.parent
  UNION ALL
    SELECT tc.Child,tc.Parent,tc.Quantity * c.Quantity
    FROM composition c, tblComposition tc
    WHERE c.Child = tc.Parent and tc.child <> tc.parent
  )
SELECT * FROM composition

Here's is the fiddle: http://sqlfiddle.com/#!17/00f6b/15

The tricky part was separating root parents from the rest, but then I found the way to do it, by comparing Child and Parent values

answered Mar 03 '20 at 11:58

Nicola Lepetit

756
8
25

1

I get the answer, but the upvote? This has nothing to do with MS Access. – Gordon Linoff Mar 03 '20 at 13:30
@GordonLinoff, I did not mark this as answer. It looks like it's inachievable in Access query. I just need some information on how to search for a question. Looks like [this](https://stackoverflow.com/questions/763016/is-it-possible-to-create-a-recursive-query-in-access) is very similar to my problem and can be solved via VBA. – Triostrong Mar 03 '20 at 13:34
1

@Triostrong . . . I added the comment to discourage people from writing answers that will clearly fail in the available tags. It is like writing a Java answer to a C++ question. – Gordon Linoff Mar 03 '20 at 13:39

Dy.Lee · Answer 2 · 2020-03-03T16:00:40.590

0

Try

   SELECT  chd as Child,(qty * [d.plan]) as Plan from 
   (select a.child as chd, a.parent, a.Quantity as qty, b.parent as id from [tblComposition] as a left join [tblComposition] as b on  a.Parent = b.child ) as c 
   left join [tblProducts] as d  on c.id = d.product

The same data were obtained with the illustrated data. I don't know if the data will change, but test it out. I tested in excel by sql.

result image

edited Mar 03 '20 at 16:00

answered Mar 03 '20 at 15:47

Dy.Lee

7,527
1
12
14

I've got the same result in Access, but check 222's plan. It should be 6840 = 5*3*456, but query returned 2280 = 5*456 – Triostrong Mar 04 '20 at 10:46

Access: how to query until final parent is found?

2 Answers2

result image