Linking Question

[Shav]

Crystal 11
I am using the following tables :
{Transactions}
{Customers}
{Customer Info)

My report is grouped by the customer ID number which is a primary key.

My report headings in the Cust ID group are as follows :
Customers.CustID
Customers.Address
Sum of Transactions.Value in USD
Sum of Transactions.Value in Foreign currency

When I try to put in the field from Customer Info.Additional Data, the report gets messed up and only shows the Customers who have a Additional Data values.
Please note not all customers have a Additional Data values and there could be some customers with null values.

I feel the Customer Info table is a funny table because it could have multiple data values.
For example,
Customer Info.Additional Data Values will have the Tax file number value, the SIC code values and probably more in the future.

What is going wrong with the report here ?
Is it my linking ?

 

[scottostanek]

Your links should be Transactions LOJ Cust LOJ Custinfo
LOJ --> left outer join
In crystal right click the line and check out join options.

You will get null returns on values which don't exist so be aware the simplest way I know is to make formulas like
if not isnull({Field1}) then {Field1} else 0

and sum THOSE instead.

YMMV
Scotto the Unwise

 

[jmd0252]

or you could pass a command to the database, that summarizes the data by cystomer,, and just link on the customer number.

 

[lbass]

I would use a left outer join FROM customers to customer info, and an inner join from customers to transactions. You should not make any selections on the customer info table, or it will in essence undo the left join. If you get multiple instances of transaction records so that your summaries are incorrect, use running totals that sum on change of some unique transaction ID, and then place the rts in the group footer section.

-LB