meets criteria

[grumbachr]

I got a one to many relationship. Its People to Medical Records. I know the age of each person at the time of the Medical Record.

What I need to is a field I can search on for anyone that we have a recored for between two ages but also showing me all their medical records.

If person has any med_record between the ages of 10 and 12 I want to see all their medial records.

How can I do this?

Thanks in advance.

[comment]

1. Find the people that meet the criteria.

2. Go to Related Record [ from Medical Records, match found set ]

---

Now that I read this, I am not sure where the age is kept. If it's in the medical record, you may have to apply the same trick twice: find the records that meet the age criteria, find their owners (by GTRR, matching found set), and finally find ALL their records as before.

Edited January 12, 2008 by Guest
clarification

[grumbachr]

My end goal is to see all of these at once in a list view. I'm really tired so I'm not thinking straight but I'm not seeing how GtRR is going to help me.

And to clarify Age is stored in the Medical records table.

My first attempt has been this, a Calc field in the People table

If ( Medical_Records::Age at Date ≥ 10 and Medical_Records::Age at Date ≤ 12 ; 1 ; 0 )

So far it working but I've not tested it much to see if it broken

.

[comment]

A calc field doesn't seem like a good idea because (a) it hardcodes the age range, and ( it only considers the FIRST related record. So if my first record is at age 8, I will NOT be found - even though I do have a later record at the age of 11.

If you find the records with age between 10 and 12 (or any other range), then GTRR from People while matching the found set, you will end up with a found set of all people that have a record within the age range. The second GTRR will find you ALL medical records of these people.

[grumbachr]

comment,

thanks so much for your suggestions. it took me a little while to understand what you were saying but once i did it really has helped.

i'd never use the GtRR like this before so i didn't really understand it at first but thanks for helping.