Querying Off Of Other Queries in Crystal 7?

[Huffdad76]

Hi guys -

I'm converting a bunch of MSAccess97 reports to Crystal Reports 7. For many of these reports, there is not just one query behind the report. In many cases, two or three queries run off of each other in order to be able to run aggregate functions on fields that were aggregated in the previous query. (i.e Summing a Sum Field, or a Sum of a Sum Of a Sum etc.)

Here's a couple query examples. qryPerformance1 runs first and then qryPerformance2 runs off of the resultset of qryPerformance1.

qryPerformance1

SELECT
LOB_Desc,
Ag_Doc_Id,
Broad_Cat_Desc,
Spec_Desc,
Count (Broad_Cat_Desc) AS Broad_Cat_Count,
FROM MyTable
GROUP BY
LOB_Desc,
Ag_Doc_Id,
Broad_Cat_Desc,
Spec_Desc;

qryPerformance2

SELECT
LOB_Desc,
Broad_Cat_Desc,
Spec_Desc,
Sum(Broad_Cat_Count) AS SumOfBroad_Cat_Count,
Count(Broad_Cat_Count) AS CountOfBroad_Cat_Count
FROM qryPerformance1
GROUP BY
LOB_Desc,
Broad_Cat_Desc,
Spec_Desc;

As you can see, I needed to run a second query in order to get the value for SumOfBroad_Cat_Count and CountOfBroad_Cat_Count because "Broad_Cat_Count" is itself an aggregate field created in the previous query.

I would then use "qryPerformance2" as the query to provide the data needed for my Crystal Report.

My question is, how do you go about running multiple queries in Crystal Reports like this? Is this even possible in Crystal?

If it is not possible, what is the best way to go about accomplishing this?

Do I have to create several subreports depending on how many levels deep my SQL queries go?

Thanks in advance for any help you can provide!

- Mike Huffman
michael.huffman@kmhp.com

 

[IdoMillet]

Just create a report based on the last Query.
Access will run the other queries that feed into that query automatically.

Cheers,
- Ido ixm7@psu.edu

 

[Huffdad76]

Thanks for your reply!

So what you are telling me is that it IS possible in Crystal Reports to build queries using other queries?

i.e. SELECT qryName.fieldname, qryName.aggregate_fieldname FROM qryName

-Mike Huffman
michael.huffman@kmhp.com

 

[IdoMillet]

Yes, I've used queries as data source for
Crystal Reports without any problems.

Cheers,
- Ido
ixm7@psu.edu