Look at a table w/o losing current data.

[2ffat]

Let's see if I can explain this clearly. We have CR 8.5 looking at SQL server on NT 4. In the DB we have a table that has all the raw material we have purchased. In another table, we have all the raw material that has been delivered. The two tables are linked via an item number. We can have data in the first table but not a corresponding item in the second table. We cannot have an item in the second table without having a corresponding item in the first table. Got it so far?

I am trying to create a report that will list all items in the first table that are not in the second table (undelivered) and all items in the second table that were delivered before a certain date. Creating two report is easy but they want one report. The problem is when I link the two tables, all undelivered items disappear from the report.

I've looked through the FAQs and forums and I cannot find a clear answer. Do I need to create a subreport, a different JOIN, an SQL query, or something else? We have several reports that have a similar problem. I'm familiar with SQL but not CR. Thanks in advance. James P. Cottingham

I am the Unknown lead by the Unknowing.
I have done so much with so little
for so long that I am now qualified
to do anything with nothing.

 

[dgillz]

Create an equal join and write a record selection formula:

Isnull({SecondTableItem})

Your join may need to include more than one field in the table, but wothout application specific knowledge I cannot help you with that.



Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[2ffat]

Thanks, I'll give that a try and get back if it doesn't work.

James P. Cottingham

I am the Unknown lead by the Unknowing.
I have done so much with so little
for so long that I am now qualified
to do anything with nothing.

 

[kenhamady]

Actually, I think Dgilz meant a Left Outer Join. The arrow should point from the first table to the second table.

Then the selection formula would be:

Isnull({SecondTable.Item})
or
{SecondTable.Date} < {YourDatePrompt}
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

 

[2ffat]

Thanks guys! It was the Left Outer Join that was tripping me up. I was already checking for null values so that's why I was confused. X-) James P. Cottingham

I am the Unknown lead by the Unknowing.
I have done so much with so little
for so long that I am now qualified
to do anything with nothing.