[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

display records that exist in table a but do not exist in table b

Posted on 2014-04-23
5
Medium Priority
?
2,209 Views
Last Modified: 2014-04-28
Hi There,

I'm using Crystal Reports XI.

I've got two tables and want to create a report which displays all the records in table a that do not exist in table b.

Is this possible?

thanks! Kristin
0
Comment
Question by:forcedexposure
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 40018331
Yes it is.

There are 2 ways
1.  Use a query with a left outer join from A to B.  Can be done by selecting the tables and setting the join to LEFT OUTER

You then suppress any records where B.ID is NOT null
Not(IsNull({TableB.IdField})

2.  Use a Crystal command with a subselect as

SELECT TableA.LIST OF FIELDS
FROM TableA
WHERE (Not (TableA.ID IN (SELECT TableB.ID FFROM TableB)))

mlmcc
0
 

Author Comment

by:forcedexposure
ID: 40018729
I want to go with option # 1 because i don't have the luxury of creating views in my tables.
but option #1 doesn't work... it's displaying all the records from table b that don't match table a. So there are 4012 lines of data when there are only 59 records in table a.

help?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40019087
A Crystal command is built in Crystal not in the database.

Can you upload the report

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 40019606
Assuming that you used the correct field(s) to link table A to table B, in that direction, and made it a Left Outer join, then it seems like mlmcc's suggestion should have worked.  I believe you could also use IsNull ({TableB.Field}) in the record selection formula (instead of using suppression).  I've done that before for just this kind of thing and it worked for me.

 James
0
 

Author Closing Comment

by:forcedexposure
ID: 40027307
Hi There! I reread your suggestion and got it to work. My problem was that i needed to do left outer joins on two sets of fields from Table A to Table B.


Thanks for your patience and your help!!


best wishes, Kristin
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question