I have a rather unique problem that sits between the solution sets I've been able to find. I have a database containing tables that need iterative select queries with updating input parameters. As an example, I have an equipment table that contains a pairing of a technology with a sister tech, and the mechanisms through which they communicate:
Table Fields:
- TechID1
- TechID1Port
- TechID2
- TechID2Port
Example: Radio communicates (through its low/high band antenna) with Basestation (through its antenna port)
I have working SQL code that runs when users select TechID1 from a Combobox and the Combobox value filters the WHERE statement. However, I need to add levels of interaction based on a just added Combobox (values:1-n). The end goal is to iterate the query as done here, but without VBA, as this solution needs to be replicated for many Access tables and seamlessly transferred to Oracle/SQL Server in the medium-term.
Example of desired nth level results based on Radio selection: List of all techs that directly interact with Radio (plus associated info), with an appended list of all techs that interact with Basestation and other 1st level results (no duplicates or trackbacks to Radio), etc to the nth level.
For the above to work without VBA, I realize there is likely a series of similar/identical queries that need to run, with the ability to analyze only the row entries for the interaction level below the current run. One simple potential solution I've looked at is nested subforms that allow users to expand higher interactions as they wish, but I can't find a way to subform and filter a table onto itself.
Big thanks in advance! J
