Multiple Querying in CR???

[tommy19]

Howdy Howdy,

Is there anyway to get around the fact that you can only run one query in Crystal Reports? I need to make a report that uses multiple queries and cannot figure this out. I am wasting my time? Please help.

Thank you
tommy

 

[kenhamady]

Depending on what you want to do, you can use subreports. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Ngolem]

It all depends on what you want to do...

1. if you want to have the equivalent of a union query, I have done this by aliasing a table and thereby collecting 2 sets of data from one table with different conditions.

2. if the tables are independent of eachother then using sub-reports might do the trick.

3. if you have a set of linked tables but you wish one record set for one report look and another set for another look....you may be able to construct the formula in the Record Select to do this with Parameters...or alternatively collect data for both possibilities and use parameter flags to supress or unsupress sections to show a different look to the report.

You really haven't told us much of your problem

 

[tommy19]

Well to explain the problem alittle better. I need to put multiple queries into a report. For example:

select cal.userid ,count (*) from cal cal, contgrps ch, contgrps cg
where cg.ref = 'FMB Challenge'
and cg.recid = ch.userid
and ch.accountno = cal.accountno
and cal.ondate > getdate() + 30
and cal.ondate < getdate() + 90
groupby cal.userid;

this query finds the total number of phone calls (in Goldmine) that are scheduled by each user longer than 30 days and less than 90 days. I have other queries that are similar to this that need to be in the report. However, they do use different database tables. Would subreports work on this??

 

[kenhamady]

Depends on how you are trying to integrate the other records. If you are trying to display each query as separate results, that would be subreports.

If you are trying to mix the records from all queries into one report, then you need to use a union query to put them into one virtual table. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[tommy19]

How would I do this... The union query?
Thanks for all of your help
tommy

 

[kenhamady]

This isn't done in CR. You write a union query in SQL. There are some examples in the forum if you do a keyword search on the words Union Query. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[tommy19]

Thank you for your prior help: I finally understand the union query, But that leads to a format problem. I would like the output to look like this:

user Calls > 30 days Calls < 30 days Total Calls etc.

in that type of format.

Currently the output has all the necessary info. in a 2 column format with all the info in one column. If possible, how can I get this information into the proper form??
thanks again,
tommy

 

[kenhamady]

Look at my FAQ in the general forum on common formulas. Read the sections on conditional totals, and manual cross-tabs. I think that might do what you want. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Ngolem]

Can you give an idea of the tables involved (ie. table names and linking fields) for all the data needs of your report.

Often complex queries and subreports are not necessary if the tables are linked properly. Personaly I use Subreports as a last resort...if there is no other way. In a large report they are a nuisance with the flashing hourglass and can slow a report down considerably.