Linking and data Problem - HELP!!

[marabou]

I have a problem with the data my report is generating. Within the Visual Linking Expert I have 3 tables - Customers, Licences and Notes. Customers have Licences and Notes.
There are Notes with the type of Licence, but they are only linked through the customer. Not every Customer has a Note.

The Visual Linking expert will not let me link Customers and Notes ( I keep getting a selection error), however it will let me link Licences and notes ????. This is causing a huge problem as it is completely messing up the data. It is also only displaying those licences which have notes.

Has anybody any ideas? I have been working on this for days!!

I am using Crystal 8 with an Oracle server.

Thanks in advance

 

[kenhamady]

What are the linking fields in each table?
What exactly is the error message? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[marabou]

The error was a dll message, selection not valid. I have got as far as realising I needed an outer join within my link, which worked. But now I need to use a multiple-row sub query ( I don't know what one is though). As the complexity of the query has increased, I have decided to make a view in Oracle 8i and SQL Plus.

I have Customers who have an id, Contacts, which are linked using the foreign key CustomerID and Licences which are linked using the foreign key CustomerID.
There are customers, who have may have none, one or many licences and contacts.

So I need to work out how to do a multiple-row subquery, as the query is producing the right results, but stops as soon as it gets to where there is more than one contact in the result set it errors. It does the same in Crystal Reports.

[Select Customers.customerID, LicenceID,
(Select Contacts.CustomerID
FROM Contacts, Customers
Where Contacts.customerID (+)= Customers.customerID
FROM Customers, Licences
Where Customers.customerID = Licences.customerID]

I can't find any references on how to do multiple-row subqueries anywhere.

Can I do them in Crystal Reports?

Thanks in advance

 

[kenhamady]

And what happened to the Notes table?
When does the error occur (linking which two fields)?
Does the error occur if there are only those two tables or only if there are three tables?

You have 2 different one-to-many joins which will give you an inflated result set, even if you get beyond this error. The simplest way to deal with this within CR is to use a subreport to get the data from one of these tables. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[marabou]

Thanks for the advice, no problem at all now I have used subreports.