Process Question

[metalteck]

In MS Access, I know that one would be able to create a query, and if needed to, could run another query based on the first query.

I was wondering, I have a report that pulls my data. Is there a way I could either make crystal run another query based on the first report and another table, etc?
We are using Crystal V 8.5 and the data is all stored on a db2 server. The inital report is called Reason Code. And this report gathers all of the codes for patients throughout a time period. After this report is generated, it is exported to access.

The exported report and 2 others are used generate the Revenue Report. But before the exported report can be linked to crystal, the location needs to be set and connected via the ODBC drivers.

The Report is a list of accounts which are sorted this way.

SELECT
TRN_FILE."TRN_NUM", TRN_FILE."TRN_PAT_ACCT", TRN_FILE."TRN_SVC_DT", TRN_FILE."TRN_SVC_CD", TRN_FILE."TRN_SVC_TYP_CD", TRN_FILE."TRN_MOD_CD", TRN_FILE."TRN_FAC_CD", TRN_FILE."TRN_INS_CD", TRN_FILE."TRN_PRV_CD", TRN_FILE."TRN_AMT", TRN_FILE."TRN_UNT", TRN_FILE."TRN_ENC_NUM", TRN_FILE."TRN_RMK", TRN_FILE."TRN_RMK2", TRN_FILE."TRN_MOD_CD2",
PALW_FILE."PALW_SVC_CD", PALW_FILE."PALW_INS_CD", PALW_FILE."PALW_MOD_CD", PALW_FILE."PALW_FAC_CD", PALW_FILE."PALW_PRV_CD", PALW_FILE."PALW_1_EFF_DT", PALW_FILE."PALW_1_PRC1", PALW_FILE."PALW_2_EFF_DT", PALW_FILE."PALW_2_PRC1", PALW_FILE."PALW_3_EFF_DT", PALW_FILE."PALW_3_PRC1",
CLT_FILE."CLT_CLT_CD", CLT_FILE."CLT_CLIN_NM",
PAT_FILE."PAT_ACCT", PAT_FILE."PAT_NM",
EOB_FILE."EOB_RCT_NUM", EOB_FILE."EOB_RCT_POST_DT", EOB_FILE."EOB_RCT_AMT", EOB_FILE."EOB_COPAY_AMT", EOB_FILE."EOB_COINS_AMT",
INS_FILE."INS_PAYOR_CD",
PRV_FILE."PRV_NM",
PINS_FILE."PINS_INS_CD", PINS_FILE."PINS_CNTRCT_NUM"
FROM
"DB2"."TRN_FILE" TRN_FILE INNER JOIN "DB2"."CLT_FILE" CLT_FILE ON
TRN_FILE."TRN_CLT_CD" = CLT_FILE."CLT_CLT_CD" INNER JOIN "DB2"."PAT_FILE" PAT_FILE ON
TRN_FILE."TRN_PAT_ACCT" = PAT_FILE."PAT_ACCT" LEFT JOIN "DB2"."EOB_FILE" EOB_FILE ON
TRN_FILE."TRN_NUM" = EOB_FILE."EOB_CHG_NUM" LEFT JOIN "DB2"."INS_FILE" INS_FILE ON
TRN_FILE."TRN_INS_CD" = INS_FILE."INS_CD" LEFT JOIN "DB2"."PRV_FILE" PRV_FILE ON
TRN_FILE."TRN_PRV_CD" = PRV_FILE."PRV_CD" LEFT JOIN "DB2"."PALW_FILE" PALW_FILE ON
TRN_FILE."TRN_SVC_CD" = PALW_FILE."PALW_SVC_CD" AND
TRN_FILE."TRN_INS_CD" = PALW_FILE."PALW_INS_CD" LEFT JOIN "DB2"."PINS_FILE" PINS_FILE ON
PAT_FILE."PAT_ACCT" = PINS_FILE."PINS_PAT_ACCT"
WHERE
(TRN_FILE."TRN_INS_CD" < 'PP1' OR
TRN_FILE."TRN_INS_CD" > 'PPP') AND
TRN_FILE."TRN_SVC_TYP_CD" = 'CHG' AND
(TRN_FILE."TRN_FAC_CD" LIKE '2M' OR
TRN_FILE."TRN_FAC_CD" LIKE 'L1' OR
TRN_FILE."TRN_FAC_CD" LIKE '24' OR
TRN_FILE."TRN_FAC_CD" LIKE '03')
ORDER BY
TRN_FILE."TRN_PAT_ACCT" ASC,
TRN_FILE."TRN_ENC_NUM" ASC,
TRN_FILE."TRN_NUM" ASC

After this information is compiled, it is exported to an access database, then exported into crystal as a separate table. The final report has the exported report, along with 2 other tables, to pull a summary of the information.

Is there a way I can avoid the step of having to export the data into access?

 

[Madawc]

You can do subreports. Subreports in detail lines slow down the report enormously. But for some business needs it may be the best solution.

The other possibility is a Stored Procedure, SQL code that can do several selections and give the result to the Crystal report. In 8.5, this was limited because you could not link it to other database records, not without using subreports. It also needs software you maybe don't have.

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