Produce a list of names based on data from two fields

[magicdds]

I have a query based on a table with 2 columns.

PatientID LinkID PatientName
16 20 Joe(16)
25 16 Mary(25)
31 12 Tom(31)
27 20 James(27)


In a form I, I will have an unbound textbox. If I type in 16 in the textbox,
I want the query to produce a list of names:
Joe (since 16 is linked to 20 in record 1)
Mary (since 16 is linked to 25 in record 2)
James (since 16 is linked to 20 in record 1 and then
20 is linked to 27 in record 4)

In other words, if patient1 is linked to patient2, then patient1 is then
automatically linked to all the patients that patient2 is linked to, and visa
versa.

Is there some way to sort out the records to give me the desired list of all
patients that are directly, or indirectly linked to each other?

Thanks for any suggestions.
Mark

 

[Allen Browne]

It would be very easy to do this with a subform.

Just create a form with a text box (or combo) for entering the 16.
Set the subform's Link Master Fields Property to the name of this text box,
and its Link Child Fields to LinkID (the matching text box in the subform.)
The subform will then list the associated people. If you set up the subform
in Continuous or Datasheet view, it will show one person per row.

You could use a list box, where its RowSource is a query that has criteria
of:
[Forms].[Form1].[PatientID]
but you would need to Requery the list box in the AfterUpdate event of the
text box.

 

[magicdds]

Allen,

Thanks for your suggestion. However, this solution would only return one
name if 16 was typed in the text box --- Joe.
It does not show that Joe is linked to James in record 4, nor does it show
that Joe is linked to Mary in record 2.

I'll try to explain a different way. If there are 5 patients in column 1
(patientID) who are all related to each other. Column 2 (linkID) shows that
Patient1 is linked to patient2
Patient2 is linked to patient3
Patient3 is linked to patient4
Patient4 is linked to patient5

If I type in Patient4's PatientID in the textbox, how do we produce a list
showing that displays:
Patient1
Patient2
Patient3
Patient5

are all related to patient4 by virtue of their links to each other?

Thanks
Mark

It would be very easy to do this with a subform.

Just create a form with a text box (or combo) for entering the 16.
Set the subform's Link Master Fields Property to the name of this text box,
and its Link Child Fields to LinkID (the matching text box in the subform.)
The subform will then list the associated people. If you set up the subform
in Continuous or Datasheet view, it will show one person per row.

You could use a list box, where its RowSource is a query that has criteria
of:
[Forms].[Form1].[PatientID]
but you would need to Requery the list box in the AfterUpdate event of the
text box.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a query based on a table with 2 columns.

PatientID LinkID PatientName
16 20 Joe(16)
25 16 Mary(25)
31 12 Tom(31)
27 20 James(27)


In a form I, I will have an unbound textbox. If I type in 16 in the
textbox,
I want the query to produce a list of names:
Joe (since 16 is linked to 20 in record 1)
Mary (since 16 is linked to 25 in record 2)
James (since 16 is linked to 20 in record 1 and then
20 is linked to 27 in record 4)

In other words, if patient1 is linked to patient2, then patient1 is then
automatically linked to all the patients that patient2 is linked to, and
visa
versa.

Is there some way to sort out the records to give me the desired list of
all
patients that are directly, or indirectly linked to each other?

 

[Allen Browne]

You're right: you said you need to go down the nested tree too.

How many levels could this need to go down? You can use self-joins to a
finite number of levels, e.g.:

SELECT tblClientInClient.PatientID,
tblClientInClient.LinkID,
Gen2.LinkID,
Gen3.LinkID,
Gen4.LinkID
FROM ((tblClientInClient
LEFT JOIN tblClientInClient AS Gen2 ON tblClientInClient.LinkID =
Gen2.PatientID)
LEFT JOIN tblClientInClient AS Gen3 ON Gen2.LinkID = Gen3.PatientID)
LEFT JOIN tblClientInClient AS Gen4 ON Gen3.LinkID = Gen4.PatientID;

For something more comprehensive this stuff from Joe Celko might help:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html