>Hi,
>Thank you. I think may be I should give you more info.
>
>

>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
>
>

>
>I tried to group by AllowCode, but only one TreatCode will be appeared.. I want Srv01,Srv02 shown too..
>
>Any ideas?
>
>Thank you

I don't think this can be done with standard SQL. I would write a loop, for instance, that gets all the possible values into separate records.

Note that There may be more AllowCodes than TreatCode, or the other way, so you would have to keep some fields empty.

Also note that your result seems to imply a correspondence between AllowCode and TreatCode in the first row. Since this is not the case, your desired output is confusing. You may want to format your report differently, for instance:

>DoctorCode DoctorName AllowCode AllowanceRate TreatCode PhyRate ClinicRate
>A001 JOHN JONES A01, A02 15.00, 10.00 Srv01, Srv02 20 80

Combining some data in a memo field seems appropriate here, but this must be done with more than only SQL, IMHO.

Another option is to have multiple detail bands, more similar to the original data.