Report showing more that one set of data from billing tables

[ComputerNurse]

I have a report with from 2 databases with just a few tables added. I am getting the data I need the but… I only want one set of data from each database.

I am comparing orders written by physicians to the associated charges stored in the finance system. .

From the orders table I am getting one set of order but info from the charge table is repeated over and over again for each unique order.

I have this information detail line. I tried moving it into the group header but I seem to loose some of the info. Any suggestions would be appreciated!!!



SELECT "PAT_ENC"."PAT_ENC_CSN_ID", "PATIENT"."PAT_NAME",
"PAT_ENC"."X_BILL_NUM", "PAT_ENC"."DEPARTMENT_ID",
"CLARITY_DEP"."DEPARTMENT_NAME", "PAT_ENC"."CONTACT_DATE",
"ORDER_PROC"."DESCRIPTION", "ORDER_PROC"."PROC_CODE",
"ORDER_PROC"."ORDER_STATUS_C", "PATIENT"."PAT_ID",
"ORDER_PROC"."AUTHRZING_PROV_ID"
FROM (("CLARITY"."PAT_ENC" "PAT_ENC" INNER JOIN "CLARITY"."ORDER_PROC"
"ORDER_PROC" ON "PAT_ENC"."PAT_ENC_CSN_ID"="ORDER_PROC"."PAT_ENC_CSN_ID") INNER JOIN "CLARITY"."PATIENT" "PATIENT" ON
"PAT_ENC"."PAT_ID"="PATIENT"."PAT_ID") INNER JOIN "CLARITY"."CLARITY_DEP"
"CLARITY_DEP" ON "PAT_ENC"."DEPARTMENT_ID"="CLARITY_DEP"."DEPARTMENT_ID"
WHERE "CLARITY_DEP"."DEPARTMENT_NAME"='PHYSICAL MEDICINE MAIN CAMPUS' AND
"ORDER_PROC"."ORDER_STATUS_C"=0
ORDER BY "PAT_ENC"."DEPARTMENT_ID"


SELECT "ENCOUNTER_CHARGE_DETAIL"."CHARGE_CODE",
"ENCOUNTER_CHARGE_DETAIL"."QUANTITY",
"ENCOUNTER_CHARGE_DETAIL"."ENCOUNTER_NO", "ENCOUNTER_CHARGE_DETAIL"."DEPT"
FROM "PATREC"."ENCOUNTER_CHARGE_DETAIL" "ENCOUNTER_CHARGE_DETAIL"


SELECT "CHARGE_MASTER"."CHARGE_DESC", "CHARGE_MASTER"."DEPT",
"CHARGE_MASTER"."CHARGE_CODE"
FROM "ACCREC"."CHARGE_MASTER" "CHARGE_MASTER"


SELECT "IDENTITY_ID"."IDENTITY_TYPE_ID", "IDENTITY_ID"."IDENTITY_ID",
"CLARITY_SER"."PROV_NAME", "CLARITY_SER"."EPIC_PROV_ID",
"IDENTITY_ID"."PAT_ID"
FROM "CLARITY"."CLARITY_SER" "CLARITY_SER" CROSS JOIN
"CLARITY"."IDENTITY_ID" "IDENTITY_ID"
WHERE NOT ("IDENTITY_ID"."IDENTITY_ID"='1000002' OR
"IDENTITY_ID"."IDENTITY_ID"='1265321' OR
"IDENTITY_ID"."IDENTITY_ID"='1265323' OR
"IDENTITY_ID"."IDENTITY_ID"='1265326' OR
"IDENTITY_ID"."IDENTITY_ID"='1324902') AND
"IDENTITY_ID"."IDENTITY_TYPE_ID"=11

 

[Madawc]

You first need to group, on some value like order number. Insert > Group and choose a field. Note that it has to be some property of an individual 'row', a detail line derived from a single record, or from several individual records from different tables (datasets).
You can then move the record details to group header or footer, and suppress the detail line. (Right-click on the section and choose Format Section.)
If the records are not all identical, you can choose Report > Record Sort Expert to put them into a particular order.

All of this is based on Crystal 10. It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10