New to Relationships...

[Alisun]

I have two databases with ovarian cancer patient data:

Database #1 - General patient info (name, date of birth, medical history, family history, etc.)

Database #2 - Clinical Trials (patient name and the clinical trial(s) in which they have participated.

Is there a way that I could make a relationship to put all the clinical trials that a patient participated in, into Database #1 ?

There are approx 400 patients, so I'm not certain whether this is possible....?

Thanks,

Alisun

[mz123]

It's recommended that you create a Patient ID# (unique per patient) in case last names are spelled incorrectly and/or later changed.

So you sould make a relationship:

PatientTable::Patient ID to TrialsTable::Patient ID

Then create a portal on the Patients table layout of TrialsTable and choose whatever fields you want to see (Trial Name, Trial Date, or whatever fields you have created in the Trial Table)

Does that help?

[John Mark Osborne]

Sounds like you might have a many-to-many relationship. In other words, one patient can participate in one or more clinical trials. And, one clinical trial can have one or more participating patients. If this is true, you will need a join table between patients and clinical trials. The join table will have at a minimum, two foreign keys for patient id and clinical trial id. While I don't have an example of your exact solution, I do have one that shows a many-to-many relationship from Invoices to Products. Maybe this will help you.

joinprint.fp7.zip

[Fenton]

As Martha says, what you really want is to use a real auto-entered serial ID in Patients. But, unfortunately, since you already have a lot of data, you will need to do a "switcheroo" method on existing data first.

1. Create a PatientID field in Patients, Option, Auto-enter, Serial number.

2. Put on layout, enterable (for now, non-enterable in Browse mode later).

3. Show All Records

4. Replace [ PatientID, Serial number, [x] update serial ]

5. Hit Replace. Now you've got a real serial ID field

6. Go to Clinical Trials. Show All Records. Create a PatientID field (not auto-enter), but same type (text or number)

7. Create a temporary relationship to Patients based on the name; call it "Patients_Name"

8. Do a Replace in the Trials PatientID field, using the related PatientID (from Patients). Looks like:

Replace [ by calculation, Patients_Name::PatientID

9. You should now have the correct PatientID in Trials

10. Create a portal in Patients, based on a relationship to Trials, using the PatientID in each table.

11. Check the "Allow creation of related records" on the Trials side

12. If you see the correct records in the portal, delete the "name" relationship

13. I forgot: Backup your file(s) first. Replace is NOT undoable (but you're doing it in new fields, so you're pretty safe. But backup anyway.

[Fenton]

Oops. I didn't catch the "clinical trial(s)" (multiple). There really should be a single record for each "patient-trial" combination; a join table as John says. (Or a multi-line field; but that's awkward, esp. for data entry later.)

To do it properly, you should break out the multiple trials per patient. If you told us what form they are in, we could likely help. But if they're all in one field, it might not be easy. Certainly possible though. Moving data from poorly designed structures is (unfortunately) a common task. On the plus side, it should only have to be done once; unless someone else is giving you data in this format.

[Alisun]

Each patient has a social security number, which is a unique identifer. Therefore, do you think it is necessary to also create a auto-entered Patient ID?

Alisun

[Alisun]

Fenton,

Yes, there is a single record for each patient-trial combination.

For example, a (fictional) patient named Clara Smith may have been in two trials, so there are two records.

All I need to transfer into Database #1 is the name of the trial (Study and Local Study#).

Please see the attached screenshot...

[Ender]

Each patient has a social security number, which is a unique identifer. Therefore, do you think it is necessary to also create a auto-entered Patient ID?

Yes. The Social security number should not be used as a relational key for a couple reasons:

(1. There are privacy concerns. Social security numbers should be protected from casual view. While relational keys aren't necessarily shown on the layout, often they are shown and even used to help with Finds.

(2. Some patients may not have a social security number, or may not wish to give it. I'm thinking of young children, new immigrants, and people who are worried about protecting their identity.

(3. If someone has has their identity stolen, they may change their social security number. This could be a nightmare to change all the relevant relational keys in their various files.

[Alisun]

Aha! Ender, thank you for listing the reasons why a Patient ID is better than a SSN# for a unique identifer.

I forgot to say thank you to Martha and John for your examples. :laugh:

Edited May 26, 2006 by Guest