2 tier relationship

[grnorton]

Hello

I am creating a data base to more efficiently generate a medical case mix

report

I have several databases (files) amongst these are

1 a case mix file containing a form/layout for the "Principle

Diagnosis"

2 I have a separate file of medical conditions which will have the

following fields

<Organ System>

<Subtype>

<Diagnosis>

thus for example

Organ System Cardiovascular

Subgroup Medical

Diagnosis Acute Myocardial Infarction

Organ System Cardiovascular

Subgroup Surgical

Diagnosis Repair of Aneurysm

Organ System Respiratory

Subgroup Medical

Diagnosis Chronic Obstructive Pulmonary Disease

Organ System Respiratory

Subgroup Surgical

Diagnosis Pleurodesis

I know how to create a relationship between the 2 files, but can I create a

'2 step' function?..

I want to be able to relate a drop down menu in the

Case Mix File <Diagnosis>

to the field in the Problem file, <Diagnosis> based on

firstly

<Organ System>

then

secondly

<Subgroup>

this means that the medical staff are more easily able to choose the list

which is specified at these 2 levels...

they can log onto the Case Mix data base and specify that they are "medical"

for example in a <subgroup> field in the Case mix file and I can create a

relation , but how can I then further refine the drop down list according to

the <organ_system> field in the Problem file?

Hope I make myself clear?

Graham

------------------

Graham

[BobWeaver]

Make a compound relationship based on both the subgroup and organ system, as follows.

Create a calculated field OrgnSysSubtyp in both files:

OrgnSysSubtyp = Organ System & Subtype

Then, create a new relationship based on this field. Now, when you make your drop down list, for the value list, choose "Use values from field...Specify...", and in the next dialog, "only related values..." pick the new compound relationship from the pop-up, and then pick the field that you want to display in your list.

[grnorton]

Hello Bob

thanks a million - it worked perfectly! I am about to post a new question as well so perhaps you may help with it too?

Graham