Search for/Exclude Identical Foreign Keys

[rdm867]

Hi All,

I am working on a database solution for a clinical research project, and I've come up against a problem. I use one table for patient information, and a related table to store outcomes data for each operation. My goal is to perform a find in the operations table, but I need to exclude any records with duplicate foreign PT_ID keys contained in the found set (duplicates arise from patients who have had more than one procedure done, but for this project only one data set for each patient will be analyzed). I would greatly appreciate any insight on how to construct a script that accomplishes this.

-Ryan

[comment]

There's a piece missing in this puzzle: if patient X has had two procedures, which one of the two will you choose? Also, you speak of creating a found set - but what will you do with it, once you have it? It sounds like you'd want to make the result a more permanent one.

[rdm867]

There's a piece missing in this puzzle: if patient X has had two procedures, which one of the two will you choose? Also, you speak of creating a found set - but what will you do with it, once you have it? It sounds like you'd want to make the result a more permanent one.

Surgical device companies will come and ask for data related to their product. I usually go to the procedures table, perform a find for operations that utilized their device, and export the outcomes study results to excel. This time the requriement is a little different, and I need to send out data for every patient that has been operated on, but I can only include data for one of their operations. For those patients that have undergone multiple operations, it doesn't matter which operation is chosen, but I need one and only one for each patient. I guess I could arbitrarily include the most recent operation. The data gets exported to excel, and is then sent somewhere to have the data analyzed and published as part of a study where I will never see or here from it again. haha.

Thanks for the Help!

[comment]

If this is for export only, sort the found set by PatientID and select the option "Group by:[PatientID]" when exporting. Only the first record in the group will be exported - so you can control which one it will be by subsorting by date.