Access - Multi level search query

[wotgoesup]

My problem:
I have a table with application ID's and the servers on which they reside, and an index for server to app. Both are many to many relationships. I need to drill down until I have all apps / servers involved or connected to one app or one server

e.g. serverA has to be relocated:
retrieve all apps for ServerA = app1, app2
retrieve all servers for app1 = serverA, serverB
retrieve all servers for app2 = serverA, serverC
retrieve all apps for serverB = app2, app3
retrieve all apps for serverC = app2
retrieve all servers for app3 = serverB
OK to stop, no unrelated servers or apps.

I know this is quite involved, but I was hoping I didn't need to re-invent the wheel - anybody have code like this?

THANKS,
Andy

 

[Chad1984]

Okay, I'm pretty confused here... it looks like you're saying you have three tables. I'll give an extremely oversimplified version of the definitions.

Table App
----------
App_ID
App_Name

Table Server
------------
Server_ID
Server_Name

Table App_Server
----------------
App_Server_ID
App_ID
Server_ID

...in other words, a standard many-to-many relationship between "application" records and "server" records?

Now, as for what you want, I'm really confused. It's very easy to create a query to list all applications for a server, or servers for an application. You could even list the server with all its applications AND all the servers that also use those applications, if you so chose. That's simple SQL. The SQL forum is a good place for that if you need help.

I trust I'm misunderstanding your post. It looks like you're suggesting that you want Access to take a server, find the applications on that server, find all the servers for every application, find all the applications for every server, and go on and on and on until you run out of connections. In a situation where you may have hundreds of servers and scores of applications on each server, wouldn't that lead to thousands of connections?

I do hope I'm misunderstanding this. IF that is what you intend, though, I'm pretty sure you'd need VBA code to do that, and there's a forum for that as well. Again, it all depends on what you want. Do you want a list of all the systems and their associated applications, connected this way? Perhaps a recursive VBA function, constantly adding system IDs to the same table/list and following down the "branches" of this tree you're developing, going to an application and looking at its associated systems, adding them to the list if they aren't already, then calling itself on those systems (the ones that aren't already on the list), etc, and then producing a query joining that table with application IDs. What is it you're looking for?

 

[wotgoesup]

Chad,
thanks for taking the time to look at this.
Currently I only have a spreadsheet! There are around 3000 server/app combinations, and typically I manually drill down usually 3, sometimes 4 levels. I'd really like to build a hierarchical view. I thought I would need one table keyed by server, app and an index to app, server. I guess my real question is how do I know when I'm done? Is that a simple SQL syntax?
Thanks, Andy

 

[SkipVought]

Hi,

"Currently I only have a spreadsheet! "

faq68-5829

You can use MS Query to "drill down" as you have described. You will have to disign the control structure. You can use Sheet Controls or Data > Validation - List as a control.

You might be better off posting in Forum707.



Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]