Using one table to get data from another?? (AREV Specific)
At 05 JAN 2005 02:27:19PM Linda M Grant wrote:
Is there any way that I can use one table that holds one record for each person (history data)to get multiple lines of data from another table?? Basically I have one table called "patient.data" with a patient number say 2503 - in the other table "weekly.data" that same patient 2503 may have 200 lines of data (2503*1, 2503*2 etc..). Is there anyway to get all data from the "weekly.data" table for a particular group of patients grabbed from the "patient.data" table where I would just have the 1st half of the key?
OR I can also select the patients from the "weekly.data" table using a criteria but I would like to select the patient by the criteria and then grab all lines of data for that particular patient that fits the criteria. So if I use a date to select the people I would like all data that goes with that person not just after the date I select (if that makes sense to anyone)
HELP!!! Is this impossible without doing a major programming project?
At 05 JAN 2005 03:49PM Wilhelm Schmitt wrote:
Linda,
Actually it is very easy to relate to foreign files.
Assuming a PATIENTS and a WEEKLY.DATA file with a common field "PATIENTID", you would:
1. In PATIENTS add a multivalued symbolic containing the foreign keys of WEEKLY.DATA by either
a) BTREE.EXTRACTing directly from the corresponding index in WEEKLY.DATA for patient=2503
(This option assumes that you have an indexed PATIENTID field in WEEKLY.DATA), or
b) by looping through (or using a for…next… statement) and accumulating the keys of WEEKLY.DATA for that 1 patient
(This option assumes that you use sequential keys for each patient-transaction.)
c) A relational index, pointing from PATIENTS to WEEKLY.DATA would also accomplish the same task. However, I prefer to stay away from relational indexes in AREV.
The values in this field (PATIENT_TX) would look like this:
2503*1
2503*2
2503*3 etc.
2. Add any number of additional multivalued symbolic fields in PATIENTS to extract particular values from the WEEKLY.DATA file, through formulas like this one:
@ans=xlate('WEEKLY.DATA',{PATIENT_TX},'NEXTDATE','X')
The new fields are then treated as any other field in the PATIENTS file, i.e. they may be used for display in windows or in a report.
Hope this helps.
Wilhelm