Printing Records that aren't in a DB 1

[reporting]

I have three Excel spreadsheets, all containing LName, FName, Address, etc. I added a column called Source that contain the data source, then converted the three spreadsheets into tables in an Access DB.

One of the tables is the master DB. I have to print all entries whose LName and FName are the same but aren't in the master DB.

Any ideas on how to create a report like this?

Thanks very much,


John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[lbass]

Can you clarify a bit? There are three tables, one of which is the master table, and you want to identify first and last names that are in one or both of the two non-Master tables but are not in the Master table? Please name the tables for us.

-LB

 

[reporting]

Hi there LB. EI is the master table. The other tables are CU and LL.

So, if EI.LName = CU.LName and EI.FName = CU.FName, I don't want to print the CU record. However, if an LName and FName record exists in CU that does not exist in EI, I want to print the entire CU record.

Also, I do not want to print the EI records...

TNX,


John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[reporting]

A bit more info... Once I have done the report for EI and CU, I will redo it, changing CU to LL.

Regards,

John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[MJRBIM]

Start with the CU and LL tables do a Left-Outer Join to the EI table.

SELECT only the records where the EI table values are NULL.

 

[lbass]

You could do this all at once by using a command:

select 'EI' as Source, EI.`fname`+' '+ EI.`lname` as Name
From EI
union all
select 'CU' as Source, CU.`fname`+' '+ CU.`lname` as Name
From CU
union all
select 'LL' as Source, LL.`fname`+' '+ LL.`lname` as Name
From LL

Then insert a group on {command.name} and then create a formula {@EI}:

if {command.Source} = "EI" then 1

Then go to report->selection formula->GROUP and enter:

sum({@EI},{command.name}) = 0

This would display any names in the either LL and/or CU that contained names not in EI.

-LB

 

[reporting]

Thanks LB. When I checked that SQL Expression prior to saving it, I rec'd the following error message:

Error in compiling SQL Expression
Database Connector Error: '42000:[Microsoft][ODBC Microsoft Acess Driver]Syntax error. in query expression 'select 'EI''.[Database Vendor Code: -3100 ]'

Any ideas on why?

Regards,


John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[reporting]

MJRBIM: that worked like a charm (at least the first 20 records were right!!!)! Thanks very much.

That being said, I don't understand why it works... Can you please explain the reasoning behind it so that I can learn from this?

Thanks again,

John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[lbass]

John,

The code should have been entered in a new report->blank->your datasource->add command. NOT as a SQL expression.

-LB

 

[MJRBIM]

In the CR "Database Expert" -> "Links" tool, always start you table linking with the table where there will always be data - never blank or NULL on the far left of the screen (in your case this is CU table).

CU --{left-outer}--> EI

A left-outer join means that the data is required in the left-hand table in the link, but is "optional" in the right-hand table in the link (in your case the "optional" table is EI).

By selecting the NULL values from EI, it returns only the records where there is data in CU that does not have a match in EI.

 

[reporting]

Thanks LB. Never tried that before...

When I did (CR XI), I rec'd this error message:

Failed to retrieve data from the database
Details: 07002:[Microsoft][ODBC Microsoft Acess Driver]Too few parameters. Expected 6. [Database Vendor Code: -3010 ]'

Regards,


John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[reporting]

Thanks very much MJRBIM, much appreciated.

I always set my CR tables up with one to many, with the table with one on the LHS and the table with many on the right. I put admin tables on the RHS

An example to creatwe an invoice from Xtreme:

Customer -> Orders -> Orders Detail -> Product Type -> Products -> Suppliers

But your selecting NULLs brings CR to a new level!

Thanks again,

John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[lbass]

John,

The punctuation/syntax is particular to your datasource/driver, and my suggestion was just to use as a guide. What was the command that you actually entered? You could look at "show SQL query" for the report when you used MJRBIM's suggestion as a guide to how you should punctuate, syntax, etc.

-LB