Link Records

[brennan]

Hi, I have a table with 3 columns and records look like this

Job Number	Predecessor Job Num	Successor Job Num
1		2
2	1	3
3	2	4, 5
4	3
5	3
6	10	11

My task is to get a list of job dependencies based on the input job number.
For example, if job number 2 is selected, the queries should return the first 5th rows because:

job 1 is the predecessor to job 2
job 3 is the successor to job 2
job 4 & job 5 are successors to job 3, which job 3 is successor to job 2

job 6 should not get selected because job 6, 10and 11 are not dependent on job 1,2,3,4, or 5

Could you please provide a hint how to do it? Is there any faster way to prepare the list?

[June7]

I know I worked with another poster on this kind of issue, maybe last year. Doubt I will ever find it again.

In this case, the multiple values in one field complicates. Is this just a text string or is it a multi-value field? I changed the 4, 5 data to just 4 so I could test a query. The following works with the example dataset.

SELECT Table1.JobNumber, Table1.Predecessor, Table1.Successor
FROM Table1, (SELECT Table1.JobNumber, Table1.Predecessor, Table1.Successor
FROM Table1
WHERE (((Table1.JobNumber)="2"))) AS Query1
WHERE (((Table1.JobNumber)=[Query1]![Predecessor])) OR (((Table1.JobNumber)=[Query1]![Successor])) OR (((Table1.JobNumber)=[Query1]![JobNumber])) OR (((Table1.Predecessor)=[Query1]![Predecessor])) OR (((Table1.Successor)=[Query1]![Successor])) OR (((Table1.Predecessor)=[Query1]![Successor])) OR (((Table1.Successor)=[Query1]![Predecessor]));

Your solution might require VBA