Count from one Table, Sum from another

[GummowN]

Or in other words a left outer join!

I have a table where I want the count of the records and another where I want the sum. However not all records in table 1 have records in table 2.

The universe has an outer join set-up. But when I create the report the count results are as a inner join.

How can I avoid this?

PS I am new to BO - but experienced with SQL

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[Madawc]

Do you mean that records in table 1 with an equivalent in table 2 are not shown?

That usually happens when you've also got a selection test for table 2. When there is no record for table 2, the selection test hits a null and stops.

One way round is to make a more complex test. I did this for a case where accounts might or might not have insurance, and I did not want to see the insurance if it was closed or withdrawn:

 (Isnull({INSURANCE.Status})
   or {INSURANCE.Status} = "Live")

If this doesn't work, remove the selection from table 2 and find some way of supressing or not counting the unwanted record.

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