MS Access Query help - "Spidering" an item

[somacore]

For my application I need to be able to compare items in a database and output how they're all related. For example:

Ticket #1 Programs:
AA39
FR08
AA05

Ticket #2 Programs:
AA39
FR08
VS01

Ticket #3 Programs:
VS01
WB10

Ticket #4 Programs:
AA01

Now let's say someone wants to do a "spider" (that's what we call it, don't hate me) on Ticket #1. The query should select all the programs from Ticket #1 and then select any tickets that have programs matching Ticket #1. The query finds Ticket #2 has matching programs, so it should then look at all the programs in Ticket #2 and select any other tickets that have programs matching Ticket #2, and so on. So, if you do a spider on Ticket #1, you'd get back:

Ticket #1: aa39, fr08, aa05
Ticket #2: aa39, fr08, vs01
Ticket #3: vs01, wb10

So far I have something conceptual like

Select Ticket, Program as Program1 from DB where ticket = ? and Program = (Select Ticket, Program from DB where Program1 = Program..someth ing

As you can see it kind of falls apart where my brain stops working right. I'm determined it can be done with one query.

Thanks for any help.

[ADezii]

I'm a little confused in that WB10 in Ticket #3 Programs has no match in any of the other Programs and the same with Program AA05 in Ticket #1 Programs, yet they are included in the Result Set, why?

[somacore]

The reason for this is that each ticket represents a complete fix to a certain production problem. If Ticket #3 were moved without the WB10 component, things would break. The reason for this data is to get a complete picture of all the code necessary for a move once it's cleared for production.

I'm a little confused in that WB10 in Ticket #3 Programs has no match in any of the other Programs and the same with Program AA05 in Ticket #1 Programs, yet they are included in the Result Set, why?

[ChipR]

I think you will need a table to hold your results. Here's how I would do it.

Keep a list of tickets processed and left to process, and a current ticket.
Keep a list of programs processed and left to process, organized by ticket #, and a current program.
While the lists are not empty,
Get the next program for the current ticket. If that's all for this ticket, then write a record to the table for that ticket and go to the next ticket and the first program. Otherwise add this program to the list for this ticket in the results.
Check that the program wasn't processed already by searching the pocessed programs list.
If it was, next. If it wasn't, get a list of all the tickets it's in with a SELECT or whatever, then add any of those tickets that haven't been processed to the list of tickets left to process.

Fortunately there is a searchable list item variable type you can use.
The List Class

This is just off the top of my head, so it could probably be refined some.
If you don't keep a list of tickets already processed, you run the risk of an infinite loop.

[FishVal]

Hello, somacore.

The following article could be helpful for you.
Producing a List from Multiple Records

Also this, not exactly the same but pretty close.

Regards,
Fish

[somacore]

Thank you FishVal, and Chip for your very helpful suggestions. I'll look into both of these and get back if I need more advice. Thanks so much!