Issue with returning all records

[alwayslrN]

I have an existing Crystal 8.5 that is omitting data.

There are 3 core tables used in the report, two of which are history tables. The issue is twofold, (1) to link the history tables the third table has to be used and (2) one of the history tables does not have records for the date being tested. Here is an extremely simplified sample of made up data, but it focuses on the fields and data that are causing my problem.

HistTab1
Loan ADate Amt
3333 2007-03-31 100
3333 2007-04-30 100
3333 2007-05-31 100
3333 2007-06-30 100
3331 2007-05-31 100
3331 2007-06-30 100

HistTab2
LRef ADate Amt
3333 2007-03-31 100
3333 2007-04-30 100
3333 2007-05-31 100
X300 2007-05-31 100
X300 2007-06-30 100

XREFTab
Loan LRef
3333 3333
3331 X300
3332 X300

A parameter is being used for the ADate. Using an ADate of 2007-06-30 for the parmeter, my issue is, no matter what way I try to do my linking (outer joins) I cannot get ALL of the HistTab1 records for 6/30/2007. Apparently, because the linking of HistTab2 to XREFTab is satisfied because records exist in the HistTab2 table for Loan 3333 in other months I am not getting a desired null record from the HistTab2 table.

Any thoughts?

 

[synapsevampire]

Now post what you need as output.

A join might work in most instances, left outer xref to hist2, left oyter hist2 to hist 1, but I would create a View on the database using a UNION ALL:

select 'hist1' source, Loan, ADate, Amt from Histtab1
union all
select 'hist2' source, LRef, ADate, Amt from Histtab2

Then join the view to the XREFTab

-k

 

[alwayslrN]

Thanks k

What I need is to use informaiton from both the HistTab1
and HistTab2 table on the same loan record. Obviously, my over simplified examples made it appear that the format of the history tables is the same.

We are currently using several pieces of data on the report. The core information that is needed is as follows:

HistTab2.LRef HistTab1.Amt HistTab2.Amt1 HistTab2.Amt2
3333 100 0 0
X300 200 400 200

Our current report is missing the first row. If it were appearing the HistTab2.Amt1 and HistTab2.Amt2 values would be 0 because the HistTab1.Loan is not in the HistTab2 table as of 6/30/2007.

The report was created in 2004 and it has a lot of information. I am trying to get this working by not having to start over.