Searching based on multiple tables.
I have the following table relationships:
Client:clientID --> <--Diagnosis:clientID--> <--referrals:clientID
I'm trying to create a layout that can search for a particular diagnosis for a particular referral.
Clients can have more than one diagnosis obviously. Also, on the diagnosis portal when they add a diagnosis to a person there is sort of a delete option. When they click the X it pastes text in a field which says diagnosis deleted on date by account name. Then the portal they see just hides records that have text in that field. They think they deleted it but for other reasons we need to keep that entry rather than truly deleting it.
What I would like to do is is create a layout where the person can search for any given diagnosis for any referral type. So all diabetics that were referred to Centre A. I want it to ignore those where the diagnosis was "deleted".
My layout is based on the referrals table, with fields on it about client and diagnosis via the related tables above. On the referrals table I created a field called diagnosis which is a calculation listing the diagnosis field from the diagnosis table. Works but leads to two problems. First one being that they may not have a diagnosis at the time the referral is made, may be made at a later date....and two it includes the ones that have been "deleted" which I don't want it to do.
Is there a better way to search for this than what I'm doing or is there something I've left out to eliminated those deleted diagnosis records and having to re lookup all the time?