I am having a challenge with what I think is a simple issue (using Crystal XI). I am trying to include records for a report from Table A (Informix DB via ODBC connection) that are not found in Table B (Access DB on current file server via Access/Excel DAO connection). Table A is on 1 db server and Table B is an Access DB on another server. I am successful linking the 2 Tables together and producing a report that includes just records where records in Table A and Table B are equal (Link - Inner Join) and producing a report where there are records in Table A and either in or not in Table B (Link –Left Outer Join). I am having difficulty figuring out how to produce a report where there are records in Table A but not in Table B (in other words exclude those that have a match).
I have been successful using Access query to complete the data selection but I don’t know how to get it to work in Crystal. A scaled down simplistic version of the query that worked for me in Access is as follows:
SELECT tableA.row_date, tableA.logid, tableA.calls
FROM LEFT JOIN tableB ON tableA.logid = tableB.LogID
WHERE ((Supports.LogID) Is Null)
The key to the query is in the Where clause “Is Null”. I am trying to do this in the Database Expert portion of the Crystal report. The current Selected Tables looks like the following:
Informix DB
Command – contains selection for tableA (certain fields and certain records)
N:\root\Access DB
TableB – actual link to table
The Link is by logid field.
I don’t see a way in Crystal to apply a command that allows selection from one db table to another db table allowing the “Is Null” Where clause to occur.
I also tried specifying in the Record selection criteria tableA.logid <> tableB.logid while using Left Outer Join and Link Type of = on the Link Options. When I used the <> I retrieved 0 records.
The basic concept I am trying to build upon is I want to be able to use a control database to allow me to manage and use as a source for allowing the selection of data from data collected on another system.
Is there a simple fundamental concept that I am missing?
I have been successful using Access query to complete the data selection but I don’t know how to get it to work in Crystal. A scaled down simplistic version of the query that worked for me in Access is as follows:
SELECT tableA.row_date, tableA.logid, tableA.calls
FROM LEFT JOIN tableB ON tableA.logid = tableB.LogID
WHERE ((Supports.LogID) Is Null)
The key to the query is in the Where clause “Is Null”. I am trying to do this in the Database Expert portion of the Crystal report. The current Selected Tables looks like the following:
Informix DB
Command – contains selection for tableA (certain fields and certain records)
N:\root\Access DB
TableB – actual link to table
The Link is by logid field.
I don’t see a way in Crystal to apply a command that allows selection from one db table to another db table allowing the “Is Null” Where clause to occur.
I also tried specifying in the Record selection criteria tableA.logid <> tableB.logid while using Left Outer Join and Link Type of = on the Link Options. When I used the <> I retrieved 0 records.
The basic concept I am trying to build upon is I want to be able to use a control database to allow me to manage and use as a source for allowing the selection of data from data collected on another system.
Is there a simple fundamental concept that I am missing?