Compare Two Tables

[eric333]

Good evening,

I've browsed the postings regarding comparing data in two tables and cannot find one that is applicable to my situation.

I have two tables in two different SQL databases. Each table has a common field (reference number). I am trying to compare the two tables to determine which records are missing from either table. I am okay with doing this through two different reports, if necessary.

This works perfectly for me using MS Access (okay, I know, I know) as the application has built in missing record queries. I'd much rather do this through Crystal Reports.

Thanks in advance for any advice.

 

[lbass]

Do the two databases use the same connectivity? I.e., are you able to reference both in the same command?

-LB

 

[eric333]

In Database Expert, I am able to connect to both databases as an ODBC (RDO) connection.

 

[lbass]

Then try creating a command (above your list of tables for that driver):

select 'A' as whichtable, tableA.refno
from tableA
union all
select 'B' as whichtable, tableB.refno
from tableB

Then you can insert a crosstab in the report header that uses {command.whichtable} as the column, and {command.refno} as the row, with count of {command.refno} as the summary.

-LB

 

[eric333]

Thanks for the prompt response. Unfortunately, that returns no results. I guess I am over thinking this. I can use the unmatched query results from Access and just have my report read from that query. Not as cool as using only Crystal, but if it works...

 

[lbass]

I don't know why you wouldn't get results--if it compiles okay--since it is just merging the fields into the same field. Can you copy the command into the thread?

-LB