Recursive VBA Query (1 Viewer)

dvdcour

New member
Local time
Today, 17:39
Joined
Jul 26, 2020
Messages
6
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

Immoderate Moderator
Staff member
Local time
Yesterday, 22:39
Joined
Feb 28, 2001
Messages
18,773
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

Super Moderator
Staff member
Local time
Today, 04:39
Joined
Feb 19, 2013
Messages
12,803
and it may be that an assembly is used in turn in another assembly as a component. Agree need to know more.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:39
Joined
May 21, 2018
Messages
4,183
Search on my handle. I have demonstrated a lot of recursive techniques on this forum.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 22:39
Joined
Feb 28, 2001
Messages
18,773
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

New member
Local time
Today, 17:39
Joined
Jul 26, 2020
Messages
6
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)

jDD7RjwJ0R.png


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

Immoderate Moderator
Staff member
Local time
Yesterday, 22:39
Joined
Feb 28, 2001
Messages
18,773
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

You've got your good things, and you've got mine.
Local time
Yesterday, 23:39
Joined
May 21, 2018
Messages
4,183
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

Immoderate Moderator
Staff member
Local time
Yesterday, 22:39
Joined
Feb 28, 2001
Messages
18,773
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

New member
Local time
Today, 17:39
Joined
Jul 26, 2020
Messages
6
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

New member
Local time
Today, 17:39
Joined
Jul 26, 2020
Messages
6
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

You've got your good things, and you've got mine.
Local time
Yesterday, 23:39
Joined
May 21, 2018
Messages
4,183
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 22:39
Joined
Feb 28, 2001
Messages
18,773
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

AWF VIP
Local time
Today, 04:39
Joined
Jul 26, 2013
Messages
7,809
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.
 

Users who are viewing this thread

Top Bottom