Help with table and relationships design

[blinton]

Hi I am struggling to add keys and relationships in access to ensure the following:



Bill is a lab technician
Jill is a doctor
doctors perform surgery
lab technicians perform tests
Frank is a patient
Fred is a patient

I want to record
Bill performs a test on Frank
Jill performs surgery on Fred

but I want to ensure that I can't record
Bill performs surgery on Fred (because Bill is not a doctor)

I have made a table of "providers" which includes providername and providertype e.g. Jill,doctor and Bill, Lab Technician
I have made a table of "providertypes" which includes providertypes e.g. doctor and lab technician
I have a relationship which means the "providertype" for Jill and Bill must be either doctor or lab technician
I have a table of "treatmenttypes" which includes treatmenttype and providertype e.g. surgery,doctor and test, lab technician
I have a table of "patients" which includes patient name e.g. Frank and Fred

I am creating a table "treatment" which includes patientname, treatmenttype and provider e.g. Fred, Surgery, Jill and Frank, Test, Bill

but I can't work out how to ensure that I can't enter Fred, Surgery, Bill in that table.

Any help would be welcome.

Cheers

[Robeen]

If you are using a Form from which to enter data into your Treatment table, then you can put some validation code in the 'Save Record' button to make certain that the mix-ups you mention do not happen.
The code itself would be pretty simple if you're familiar with using VBA code.
If not, you can research the DLookup() function to start with and you can see how it can be used to take a value [Eg: ProviderName] from your Form and return a value [Eg: ProviderType] from the Providers table.

For example, if Bill was being entered to perform Surgery then you might have something like this:

If DLookup("ProviderType" , "Providers" , "ProviderName = 'NameOnForm'") = "Lab Technician" Then
    MsgBox "Lab Technicians do not perform Surgery . . ."
End If

This line:
If DLookup("ProviderType" , "Providers" , "ProviderName = 'NameOnForm'") = "Lab Technician" Then
is saying:
If the value in the field "ProviderType" - in the Table "Providers" - where the ProviderName is the NameOnTheForm = "Lab Technician" Then display the Message Box.

This is just one way to get you started. I know I didn't directly answer about the relationships . . .