If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Produce a list of names based on data from two fields
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 |
#2
|
|||
|
|||
Produce a list of names based on data from two fields
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "magicdds" wrote in message ... 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? |
#3
|
|||
|
|||
Produce a list of names based on data from two fields
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 "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "magicdds" wrote in message ... 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? |
#4
|
|||
|
|||
Produce a list of names based on data from two fields
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 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "magicdds" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|