[EpiData-list] RELATE QUERY

epidata-list at lists.umanitoba.ca epidata-list at lists.umanitoba.ca
Tue Mar 14 04:20:56 CST 2006


Hello Paul,

Yes, you're right: in each "child file" there must be the clinicnr 
(declared as "key") and, in your case, there must be also another id-number 
in each child file declared as "key unique".
Usually, it's convenient to define a "visitnr" in each child file and to 
concatenate "clinicnr | visitnr" in a unique id-number

As far as the relations between files are concerned, I would recommend to 
relate each child file to the master file (an not to relate the first child 
file to the master file, then the second child file to the first child 
file, and so forth). Please note that this can be done anywhere in the 
master file (i.e. at a different place for each child file or at a same 
place). Please note also that you can decide to open a child file or not, 
according to the result of a question.

Hereafter is an example of what you can do. Hope it helps.

Happy computing!

Yves


************
Master file (for example clinic.chk for clinic.rec file)
************

CLINICNR (better if defined as string)
  key unique
  mustenter
END

VAR1
  ...
END


VARXXX
  ...
  AFTER ENTRY
     HELP "do you want to enter visit data for this patient?  (Y/N)" 
KEYS="YN" TYPE=CONFIRMATION
      IF RESULTVALUE=2 THEN
         GOTO nextvar
      ELSE
        IF RESULTVALUE=1 THEN
         RELATE clinicnr VISIT.REC
        ENDIF
      ENDIF
   END
END

...


VARYYY
  ...
  AFTER ENTRY
     HELP "do you want to enter blood data for this patient?  (Y/N)" 
KEYS="YN" TYPE=CONFIRMATION
      IF RESULTVALUE=2 THEN
         GOTO nextvar
      ELSE
        IF RESULTVALUE=1 THEN
         RELATE clinicnr BLOOD.REC
        ENDIF
      ENDIF
   END
END


etc.


************
Child file (for example visit.chk for the visit.rec file)
************

CLINICNR  (string variable)
  key
END

  ** please note that the above field will be automatically filled **

VISITNR   (string variable)
   key
   mustenter
END

VISITID   (string variable)
   key unique
   BEFORE ENTRY
     let visitid=clinicnr+visitnr
    goto nextvar
   END
END





A 10:15 14/03/2006 +0200, vous avez écrit :
>
>
>Dear epidata
>
>I would like to set up a database for a clinic. The 4 rec files are history,
>visit, bloodresults and treatment. The clinicnr of the patient is the unique
>id that I would like to carry over between these rec files. Secondly the
>number of  records for visits/bloodresults/treatment for each patient with a
>specific clinicnr can be more than one over time. However history only has
>to be completed once (can be mother file?).
>
>When creating a relational database am I right if I say that  I cannot keep
>clinicnr as the unique nr in every rec file as that will only allow ONE
>record with that clinicnr and I need to be able to do multiple records eg
>for visits on that one clinicnr. The House.rec etc example does not really
>help me with this as the visit rec file is the last rec file. I have 3
>"children files" following one after another.
>
>Please help with the check files to relate these 4 files whilst keeping
>clinicnr as the unique id but allowing multiple records per clinicnr.
>
>Thanks
>
>Paul
>
>
>
>Prof Paul Rheeder
>
>Division of Clinical Epidemiology
>
>Faculty of Health Sciences
>
>University of Pretoria
>
>PO Box 667
>
>Pretoria 0001
>
>Tel 27 12 354 1488
>
>Fax 27 12 354 1489
>
>
>
>
>
>
>
>_______________________________________________
>EpiData-list mailing list
>EpiData-list at lists.umanitoba.ca
>http://lists.umanitoba.ca/mailman/listinfo/epidata-list

Yves MARTIN-PREVEL, Epidemiologist, MD, PhD
Nutrition Unit (WHO Collaborating Center for Nutrition)
Institut de Recherche pour le Développement (IRD)
P.O. Box 64501
34394 Montpellier Cedex 5 (France)
Tel: +33 4 67 41 61 00    Fax: +33 4 67 41 63 30
http://www.mpl.ird.fr/


More information about the EpiData-list mailing list