[b]How do I use multiple/different datasources in one report?[/b]

[staciann]

Hi. I am trying to create a report and am not sure if it is even possible to do what is necessary:

I have table tblReplyCards from an Access database along with tables PJProj and SOAddress from a SQL database.

tblReplyCards holds a list project numbers (PN) for which a customer sent in a response card at the completion of their job. PJProj holds the customer ID (CustID) numbers for each PN (each customer ID can have multiple PNs). SOAddress holds the contact info (address, phone, email) for each customer ID.

tblReplyCards links to PJProj by field PN
PJProj links to SOAddress by field CustID

All I need to do is take the list of numbers in the PN field from tblReplyCards and pull the customer contact info from SOAddress that matches up

I have tried just linking them but I get Database Warning "More than one datasource or a stored procedure has been used in theis report. Please make sure that no SQL Expression is added and no server-side group-by is performed."

I am not adding any groups but when I put the info from SOAddress into the details section and refresh the report, I get thousands of records and there should only be about 600.

Is there a way to do this? If not by linking, another way?

 

[SMosley804]

have you taken a look at the join types you are using to select the records. you may also want to try selecting distinct records;

 

[lbass]

It depends upon your goal. If you want the customer name and address, then try inserting a group on the customer and placing the desired info in the group header and then suppressing the detail and group footer. This should work if there is only one address per customer.

-LB

 

[staciann]

The link from tblReplyCards is linked to PJProj by inner join and PJProj is linked to SOAddress by a left outer join.

The option to select distinct records is depressed (unable to be checked).

There is only one address per customer but it says I'm not allowed to group anything because the data is from two different sources. Crystal will actually give an error and shut down if I even attempt to group. I am using version 10.