Comparing 2 tables for differences.

[pip0]

I'm wanting to compare two tables and pull out the non matching records from table A that does not exist in table B. The two tables have a common Field in each of them.

Thanks

Michael

 

[IdoMillet]

SELECT ... FROM Table_A
WHERE {Common_Field} NOT IN
(SELECT {Common_Field} From Table_B)

Cheers,
- Ido ixm7@psu.edu

 

[dgillz]

If you cannot enter a SQL statement, use a not equal join in your table links. You must connect via ODBC to do this.

Alternatively, if you do not have an ODBC Connection you can use an equal join and put a formula ;ike this:

IsNull({Field that should be in both tables})

in your record selection formula. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[pip0]

SELECT ... FROM Table_A
WHERE {Common_Field} NOT IN
(SELECT {Common_Field} From Table_B)

The tables that I'm accessing are Foxpro Dbases, I can not access via ODBC. The above command lines that you gave do not appear to work.

I entered the following:

Select r_cssmst.'comp_id'
FROM 'r_cssmst' r_cssmst
where {r_cssmst.'comp_id'} NOT IN
(Select {eis_css.'comp_id'}
FROM 'eis_css' eis_css

And continue to get the error message: The remaining text does not appeat to be part of the formula.

Any suggestions on this?

Thanks

 

[IdoMillet]

This is not a formula, it is a SQL statement that should be created in Crystral SQL Designer or a Query/View in your Database.

Cheers,
- Ido ixm7@psu.edu

 

[dgillz]

Pip0-

That was SQL not a crystal formula. Since not everyone does SQL I gave you some alternatives.

There are at least 2 foxpro ODBC drivers, because I have 2 of them installed on my machine. Just setup a DSN in control panel, use a foxpro driver, point it to your data and I am sure this will work.

Let me know if you have any questions. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[kenhamady]

I assume that you are using a 'datafile' connection. This means that when you link from TableA to TableB you see a colored tab indicating where the index is in TableB.

Try this selection formula:

IsNull({TableB.field})
or
{TableB.field} = ""
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Timbe]

Hi Pip0

IF you follow the instructions (prior response from one of the instructor types) about connection to the Foxpro dbf, then you may be able to use the table link. In the Database (Visual linking expert), set the link option to != (a selection called not equal to) and drag the link between the two fields key fields . . .

Timbe

 

[pip0]

Thanks, to those of you who have repsonded, I was to complete this by using the IsNull function. Does any one know some where we can search to see what all functions are available and what they do?

Thanks

 

[kenhamady]

The functions are all listed in the formula editor.
You can look them up in the Crystal help to see what they do. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com