Problem with multi-table report

[Icebun]

Hi,

I'm trying to write a report based on three tables linked like this :

Table 1 ------- Table 2
\-----------Table 3


Table 1 is the 'main' table in the report and holds details of our financial codes.
Tables 2 and 3 hold details of financial transactions, and are both linked to table 1 on the financial code.
I want to include all transactions from table 2, but only the unposted transactions from table 3.

I've included a parameter in the select expert to exclude any transactions that are unposted in table 3.

I'm using left outer joins to link the data from table 1 to table 2, and table 1 to table 3.

When I run the report, it is only including records on the financial code from table 3 (the one with the unposted transactions).

How can I get round this ? If I were to write this in Access, I would do a separate query that selected transactions from table 3 that are unposted, then link this query into the main report. I've tried this in Crystal, by writing an SQL query and saving it as a qry file, but Crystal won't let me use this qry file in the report.

I'm using Crystal 7.0.1.100.

 

[kenhamady]

You can't link to a QRY file, but you could write the entire Query in one QRY and report off of it. Also, if tables 2 and 3 can have multiple matches to table 1, wouldn't that generate redundant data?

Anyway, you can't do an outer join and then put criteria on the outer table, because that cancels the effect of the outer join on the records returned. You might need to use a subreport to get the data from table 3. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Icebun]

Thanks Ken,

I think I would have a problem using a subreport, as the data all needs to be on one line as follows :

FINANCIAL CODE----DATA FROM TABLE 2----DATA FROM TABLE 3

I think I may have to do this in Access.