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
Solved

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

Posted on 2014-04-23
5
1,685 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
  • 2
  • 2
5 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 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 100

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 34

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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

Suggested Solutions

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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 …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

789 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