Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 584
  • Last Modified:

Comparing Data in two tables

I have a Crystal Report that has two tables with common data (Customer info) with a Link on the Customer Number.  I have a Left Join on this link.  I need to be able to filter the data that is in Table1 that is NOT yet in Table2.  Can someone tell me how to do this?
0
nursecore
Asked:
nursecore
1 Solution
 
JayConverseCommented:
IsNull({table2.field}) = True
0
 
GJParkerCommented:
Add in a selection formula of

IsNull({table2.customer_number})
0
 
mlmccCommented:
You can't do it with a selection formula in Crystal.  Crystal turms the left join into an equal or inner join when you filter on the second table.

You can do it through suppression based on the null field

mlmcc
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
James0628Commented:
Try the IsNull test that the others suggested.  What mlmcc said about LEFT OUTER JOIN's in CR may be generally correct, but in my experience, doing an IsNull test on a field in the joined table is one exception to that.  I've done it before, and just tried it again, and it works for me.  In the test I just did, I did a LEFT OUTER JOIN from tableA to tableB and then used IsNull on a field in tableB as my record selection, and got the records from tableA with nothing in tableB.

 James
0
 
nursecoreAuthor Commented:
Actually I was able to use the second solution above.

Add in a selection formula of

IsNull({table2.customer_number})

Now I have another issue to resolve.  I needed to add a detail table they has the items billed to a customer.  When I run the report it shows the correct information, but shows it numerous times because (table3) has several detail items for each customer.  I just want it to show one line per customer. How can I do this??  I use table3 to filter out clients that had billing items.  It is only used in the selection formula.
0
 
James0628Commented:
Hmm.  This statement:

 > ... shows it numerous times because (table3) has several
 > detail items for each customer.

 seems to contradict this:

 > I use table3 to filter out clients that had billing items.

 If you're using the record selection to _exclude_ the customers that had billing items, then there won't be "several detail items" for the customers on the report.  There won't be any detail items.

 Unless there are different types of details and you're only excluding the customers that had certain types of details, but leaving the customers that had other types of details.  If that's the case, the following should help.

 The fact that you're only referencing table3 in the record selection formula doesn't matter.  You're reading the detail table now, so the report will read one record for each detail, and if you don't exclude those details from the report, you'll get multiple detail records for those customers.

 Just add a group on the customer ID field and put the fields that you want to see in the group header or footer, then suppress the detail section.  The group header and footer will be produced once for each customer.

 James
0
 
James0628Commented:
Oh, yeah.  For the record, the first solution (IsNull({table2.field}) = True) is really exactly the same as the second one.  It's a slightly different form, but it's really the same solution.  Just something that you might want to keep in mind when you're ready to close the question and assign points.

 James
0
 
James0628Commented:
Technically, JayConverse posted the same solution 2 minutes before GJParker, although Jay didn't specifically say that it was supposed to be a record selection formula.

 James
0
 
James0628Commented:
OK.  Fair enough.

 James
0
 
thermoduricModeratorCommented:
Starting the automated closure procedure to implement the recommendations from the CV.

- thermoduric -
EE Community Support Moderator
http://www.experts-exchange.com/Q_26662158.html

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now