Solved

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

Posted on 2014-04-23
5
1,481 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
Comment Utility
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
Comment Utility
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
Comment Utility
A Crystal command is built in Crystal not in the database.

Can you upload the report

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now