Recursive VBA Query

[dvdcour]

Hi All
I am currently working on a traceability Inventory DB with a recursive table for the inventory tblInventory which is joined to a many to many table tblTransactions
how can I use VBA to find all the incoming goods going into a specific outgoing product without knowing how many levels of transactions are made during the production process

Thanks for your help

 

[The_Doc_Man]

You need to tell us a little more here. I'm going to make some inferences. Either confirm or correct me.

You've got some sort of "assembly" or "kit" that has a bunch of components. Both the assembly and the components are a part of your inventory. You want to be able to reference the name/ID of the assembly and see the components. You probably have more than one assembly and it is probably the case that you have multiple assemblies that share overlapping components at least to some degree.

So the question is, what level of detail do you want to see? Are we talking not only component ID but component quantity as well? And how deep is deep in terms of recursion?

I'll suggest ahead of time that you will probably need to write some VBA code for this. My first thought would be to create a temporary table to hold your list of "stuff" and give it the "assembly." Then write a loop inside a subroutine that, for each non-elementary item, loads up the components and then marks that non-elementary item. And it repeats until no entry in the temp table is unmarked except for the elementary items.

 

[CJ_London]

and it may be that an assembly is used in turn in another assembly as a component. Agree need to know more.

 

[MajP]

Search on my handle. I have demonstrated a lot of recursive techniques on this forum.

 

[The_Doc_Man]

Concur that MajP is a good resource. I've done some recursion too. Some others here have dabbled in it as well. But in order to give more specific advice, we need to understand the problem.

 

[dvdcour]

Ok so ill try and explain as best I can

What I am looking for is the associated EDNumbers and HarvestDocsNumbers from tblInventory (incoming goods) that are the source for a specific loadout

there are 3 tables involved
tblEDNumbers (these numbers are imported from via an external .xlsx file downloaded from a website)
tblInventory (Recusive)
tblTransactions (many to many RecursiveLink)

incoming goods have an ED number or a Harvest number
1 ED number may have a number of different incoming products which is normally given an item number so there are 2 different fields for the ED number itself and for the Item number
EDNumber: NZL2020/APET189/12345
ItemNo: 1 (Individual Product)
EDNumber: NZL2020/APET189/12345
ItemNo: 2 (Individual Product)
goods may skip certain steps of production so their specific length of recursion may be less than others
production has multiple steps so for each step there is a recursion for example

Harvest Declarations/Honey Extraction Records/Drum Inventory/Creaming Batches/Filled Jars Inventory/Gift Packs Inventory/Loadouts
ED Numbers/Drum Inventory/Creaming Batches/Filled Jars Inventory/Gift Packs Inventory/Loadouts
Drum Inventory/Filled Jars Inventory/Gift Packs Inventory/Loadouts
Drum Inventory/Filled Jars Inventory/Loadouts
Drum Inventory/Loadouts

Then rework product as well

 

[The_Doc_Man]

You will not write a single query to do the recursion. Or at least, I doubt you can do so, particularly if you have multiple variable depth of recursion. You will need to use something like a temp table to list what you find by recursion. Re-read my post #2 as one way to do this, but not necessarily the only way to do this. However, if you have this as a shared DB, life gets even harder because you can't use the technique of marking the records to actually display ... because someone else may be doing a similar search.

You have told us the "what" - but now we need to a bit more about the "how you will use it" (and "who else will use it") factors.

 

[MajP]

To be clear there is no recursive query in Access. The is in other rdms like Oracle and I think SQL Server. So you normally have to write to a temp table as mentioned. Any chance you can post some tables and example output?

 

[The_Doc_Man]

Thanks for confirming that, MajP. I looked for such a thing when I was doing my Ancestry work and came up blank. I'm not sure that I recall seeing the ORACLE version of recursive queries but the truth is that I was the product admin, not the chief DB designer. I wasn't expected to know more than a few minimum probe methods and enough to define new users & give them their roles, rights, and privileges.

 

[dvdcour]

Ok so the purpose of this is to raise outgoing EDs for the company I'm working on the DB for
I have done it differently before but the issue is the rework can make it very difficult I made a union query to search for the data but it was not a recursive table structure which allowed for the use of queries
and yes a lot of temp tables were used
It would make a temp table with the data that table is used to complete the process of building the outgoing ED its about a 4 step process finally converting to an XML file .txt
which is then uploaded to a website

There should only be one user running this particular search at any given time

 

[dvdcour]

Cant it be done using VBA I believe Markus G Fischer did an example of hierarchies I'm still looking at it to see how it works

 

[MajP]

Cant it be done using VBA

Yes, but that is not a recursive query. In other rdms you can do true recursive queries.
You can do all kinds of recursion in code, but that is not the same as a true query. Here are some lengthy examples. The first has a very generic approach for recursion.

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

Would monitor size or resolution have anything to do with it and/or having a modal form? I'll try again when I get back home.

www.access-programmers.co.uk

Question - calculate inbreeding

I do have a difficult project that is similar to what you are on working, that is why I approach you. OK sounds interesting. I would just post it and people will reply. It may be an area that someone has expertise. Unless it is really COI because now I know more about that than ever wanted...

www.access-programmers.co.uk

 

[MajP]

@The_Doc_Man
A true recursive query in Oracle

Oracle Live SQL - Tutorial: Hierarchical Queries: Databases for Developers

SQL

Understanding SQL Server Recursive CTE By Practical Examples

In this tutorial, you will learn how to use the SQL Server recursive common table expression (CTE) to query hierarchical data.

www.sqlservertutorial.net

 

[The_Doc_Man]

Thanks, MajP. Like I said, I knew a lot about basic SQL and ORACLE structure, but less about the advanced stuff. That was because I installed it and did the user security, but the app team did all the fancies. Interesting. But then, ORACLE was the "cream of the crop" at the time we were using it. You would expect it to have proprietary bells and whistles.

 

[Minty]

CTE's are incredibly useful in SQL server.
I've used them a number of times for things like employee hierarchies, and recursive part number replacements.