Finding records in one table that are not in another

[grs]

For every record in table A, there should be at least one corresponding record in table B. The common field is LOCATION. I need a list of all location #'s that are not in table B. This is probably a very easy thing to do, but I'm having an awful time accomplishing it. Can anyone help out?

 

[kenhamady]

Link the tables using a left outer join from A to B.

Then use a selection formula:

IsNull ( {tableB.Location} ) Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[Dond12]

How do you create a left outer join? I tried your technique, which could be quite useful, but when I tried to link two tables, the only kind of join that CR would allow as an "even" join. How do you actually define the type of join you want? Is there supposed to be a dialogue box that pops up when you right-click or something?

 

[kenhamady]

Yes, if you click on the arrow it should turn white, and then you will get an options button which will allow you to select the join type. This assumes that you are connecting via ODBC. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[Dond12]

I tried your technique, but because I am simply linking two Foxpro DBF files, the choices are greyed out -- I can see that "Even" is selected, but I can't select anything else from the list. In other words, your technique works if you are connecting via ODBC, but otherwise not. Is there anything else I can try?

 

[kenhamady]

In dbase there are no ODBC join types, all joins will behave as left outer joins. Make sure the table that has no record is on the right. Then use the formula that I have described for selection.

Don't add anything else to the selection formula that comes from this outer table, or it will cancel the outer join. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[Dond12]

thanks