What is an approach to find the end item in a Bill of Materials with starting part in Access? Arrays? Recursive Function?

I’m looking for an approach to start, not necessarily a full solution. My question is how do I construct a program that takes a part number and goes up each level in a Bill of Materials, checks if the Next Higher Assembly is listed in a separate table while still keeping in mind the first part number that the user inputs? I have included example data and how the program would work in my mind.

I believe I would need to use arrays or some kind of recursive function of some sort. I don’t really know what would be the best approach besides the SQL to find the Next Higher Assembly.

I have a table with part numbers and their Next Higher Assembly like this that gives the Bill of Materials for various part numbers called tbl_PartandNHA:

Part Number NHA
Part A  Part L
Part A  Part M
Part L  Part S
Part M  Part S
Part M  Part R
Part S  Part Y
Part S  Part Z
Part R  Part Y
Part B  Part N
Part N  Part Q
Part Q  Part W

I also have another table with a list of part numbers that meet some criteria. I’ll call this tbl_PartMeetsCriteria.

Part Meets Criteria

Part Z
Part Q

For Part W, the bill of materials look like this:

Part W
    Part Q
        Part N
            Part B

The program is given a part number and climbs up the bill of materials looking for a part number that is listed in tbl_PartMeetsCriteria. The algorithm would work like this: Find NHA of Part B. The NHA is Part N. Check Part N in tbl_PartMeetCriteria. It is not in the table so find NHA. It is Part Q. Check Part Q in tbl_PartMeetsCriteria. It is in the table. Stop routine and show a msgbox “Part Q is in the table. Part B builds Part Q” In reality, my program will store the match elsewhere.

Now we have Part A to analyze. The Bill of Materials with Part A is written here.

Part Y
    Part S
        Part L
            Part A
        Part M
            Part A  
    Part R
        Part M
            Part A
Part Z
    Part S
        Part L
            Part A
        Part M
            Part A

The program will see that Part A has two NHAs (Part L, Part M). The program will check each of these in tbl_PartMeetsCriteria. Then the program must branch out. It has to look for the NHA of Part M and then the NHA of Part R. It will turn up with no matches. Then it has to go back and check the NHA of Part L then Part S to look for matches within tbl_PartMeetsCriteria. The NHAs of Part S is Part Y and Part Z. The program will find a match with Part Z and say “Part Z is in the table. Part A builds Part Z”

My question is how do I construct a program that can go up each option of the bill of materials to find a match?

I see it as a series of nested arrays like this:

Part A (Part L (Part S (Part Y, Part Z)), Part M (Part R (Part Y), Part S (Part Y, Part Z)))

I understand how to query something using SQL. I understand the basics of arrays and FOR loops, but I don't know if that is the right approach. The problem is that the example data is a simplified version of the Bill of Materials. There are thousands of connections and a lot more levels depending on the lower part number. The program would need a dynamic amount of arrays within arrays to store the options and investigate each possible thread of next higher assemblies until it finds a match or gets to the highest level. How could I do this? Is there a better option than arrays?

Solution

The real concern is the way you are allowed to edit your source data. Circle reference would cause an infinite recursion and that's why any recursion setup never allow direct input. Any input has to be validated.

Anyhow here is some recursion samples using VBA followed by the answer of your question where you can read and expand to suit your needs.

Below code will recursively search for the next element until no next element is found. F(x) = F(f(x))

Public Function RecursiveSingleNode(PartNumber As String, Optional NHA As String, Optional recursionDepth As Integer) As String
'Will return the last element of a first found node!
'
    On Error Resume Next
    If PartNumber = "" Then Exit Function

    If (Nz(DLookup("nha", "tbl_PartandNHA", "partnumber='" & PartNumber & "'"), "") <> "") Then
        RecursiveSingleNode = RecursiveSingleNode(Nz(DLookup("nha", "tbl_PartandNHA", "partnumber='" & PartNumber & "'"), ""))
    Else
        RecursiveSingleNode = PartNumber
    End If

End Function

This code is same as above but checks whether the value is found in your meetCriteria table.

Public Function RecursiveSingleNodeFindMeet(PartNumber As String, Optional NHA As String, Optional recursionDepth As Integer) As String

    On Error Resume Next
    If PartNumber = "" Then Exit Function

    If (Nz(DLookup("partnumber", "PartMeetsCriteria", "partnumber='" & PartNumber & "'"), "") <> "") Then
        RecursiveSingleNodeFindMeet = Nz(DLookup("partnumber", "PartMeetsCriteria", "partnumber='" & PartNumber & "'"), "")
    Else
        RecursiveSingleNodeFindMeet = RecursiveSingleNodeFindMeet(Nz(DLookup("nha", "tbl_PartandNHA", "partnumber='" & PartNumber & "'"), ""))

    End If

End Function

In your example table, you have multiple nodes starting with same element. like A, L..; A, M.. each node has to be searched separately. For that you need a list of all nodes to search. Use standard sql and couple it with recursive method.

In theory, you will list all nodes

1> A-L, L-S , S-Y,
A-m, m-s, s-y,

this is same for each sub nodes for M and S. Hence you need this listing function to be included in your recursive function. So it becomes

Get the next element
Retrieve all nodes for the next element
search each element recursively and check if found in matching criteria table.
Do this for each next element in a node until node reached end

to find all nodes starting with user input:

Public Function FindNHA(PartNumber As String)

    Dim SQL_GET As String
    SQL_GET = "SELECT * FROM tbl_PartandNHA WHERE(partnumber like '" & PartNumber & "')"

    Dim MyRs As Recordset
    Set MyRs = CurrentDb.OpenRecordset(SQL_GET)
    If Not (MyRs.BOF Or MyRs.EOF) Then

        Dim Result As String

        While Not MyRs.EOF
            'Recursive method to find the part matching in partmeetcriteria table
            Result = FindNHAR(Nz(MyRs("partnumber"), ""), Nz(MyRs("nha"), ""))
            If (Result <> "") Then
                FindNHA = Result
                Exit Function
            End If

            MyRs.MoveNext
        Wend
    End If
End Function



Public Function FindNHAR(PartNumber As String, Optional NHA As String, Optional recursionDepth As Integer) As String
    'Recursively search for next element and check if it's found in your PartMeetsCriteria. Return blank if not

    On Error Resume Next
    If PartNumber = "" Then Exit Function

    If (Nz(DLookup("partnumber", "PartMeetsCriteria", "partnumber='" & PartNumber & "'"), "") <> "") Then
        ' if partnumber is found in meetsCriteria table return it
        FindNHAR = Nz(DLookup("partnumber", "PartMeetsCriteria", "partnumber='" & PartNumber & "'"), "")
    ElseIf (Nz(DLookup("partnumber", "PartMeetsCriteria", "partnumber='" & NHA & "'"), "") <> "") Then
            ' if NHAis found in meetsCriteria table return it
        FindNHAR = Nz(DLookup("partnumber", "PartMeetsCriteria", "partnumber='" & NHA & "'"), "")
    Else
        If Not NHA = "" Then
            'For each element, check if it has multiple nodes and search each element in each node again, starting with NHA
            FindNHAR = FindNHA(NHA)
        Else
'Same as above since NHA is empty, use partnumber
            FindNHAR = FindNHAR(Nz(DLookup("nha", "tbl_PartandNHA ", "partnumber='" & PartNumber & "'"), ""))
        End If

    End If

End Function

Usage: try this in your immediate window

?FindNHA("Part S") Part Z
?FindNHA("Part A") Part Z
?FindNHA("Part B") Part Q
?FindNHA("Part R") ""
?FindNHA("Part M") Part Z
?FindNHA("Part N") Part Q

You should be aware this is just a proof of concept. You can use this to understand VBA's recursion capabilities but you must take additional security measurements before implementing this. You may shorten few lines of codes using sub functions. I leave the bigger version for you to understand. RecursionDepth is for you to implement.