You've set up two relationships:

Doctor <-- Doctor_Treatment --> Treatment
Doctor <-- Doctor_Allowance --> Allowance

However, the results that you want implies a third relationship not set up in your tables:

Treatment <-- Treatment_Allowance --> Allowance

This would let you show that Treatment Srv01 would have the Allowance A01 and Treatment Srv02 would have the Allowance A02.


Steven T. Cameron



****************************************

Table: Doctor
cCode cName
A001 JOHN JONES

Table: Treatment
cCode cDesc PhyRate ClinicRate
Srv01 ServiceA 20 80
Srv02 ServiceB 50 50

Table: Allowance
cCode cDesc Rate
A01 TRANSPORT 15.00
A02 MEAL 10.00

Table: Doctor_Treatment
TreatCode DoctorCode
Srv01 A001
Srv02 A001

Table: Doctor_Allowance
AllowCode DoctorCode
A01 A001
A02 A001

Result generated by joining all tables
DoctorCode DoctorName AllowCode AllowanceRate TreatCode PhyRate ClinicRate
A001 JOHN JONES A01 15.00 Srv01 20 80
A001 JOHN JONES A02 10.00 Srv01 20 80
A001 JOHN JONES A01 15.00 Srv02 50 50
A001 JOHN JONES A02 10.00 Srv02 50 50

But I want to show it like:
DoctorCode DoctorName AllowCode AllowanceRate TreatCode PhyRate ClinicRate
A001 JOHN JONES A01 15.00 Srv01 20 80
A001 JOHN JONES A02 10.00 Srv02 50 50

******************************
Table: Treatment_Allowance
TreatCode AllowCode
Srv01 A01
Srv02 A02
******************************