Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Records not in a table

Status
Not open for further replies.

NiteClerk

Technical User
Nov 9, 2009
15
US
Hi, Using CR 2008 version 12.0.0.683

I have a single table and a list of records. I want to find which records are not in the table.
I've tried: (Equip number list greatly shortened)
Code:
// No Equipment record
{EQUIP.SITEID} = "BJH" and
({EQUIP.EQNUM} in 
["NE00020283",
"NE00020310",
"SC00021070"])
and IsNull({EQUIP.EQNUM})
I've also tried LBass' solution in thread
Code:
// No Equipment record
{EQUIP.SITEID} = "BJH" and
not({EQUIP.EQNUM} in 
["NE00020283",
"NE00020310",
"SC00021070"])

And a number of other permutations. Any advice?

Thanks. -brain frozen Bob-
 
Update. I just realized that in my Design section I'm trying to print the EQUIP.EQNUM
Which if it does not exist, will of course not print. So how do I print the Equip Numbers from my list that is not in the table?
 
Hi,
To do that you need to create a table with all the possible numbers - then you can use a SQL command instead of picking from the tables and do something like this:
Code:
Select EQNUM
from FULLTABLEIBUILT
where 
FULLTABLEIBUILT.EQNUM
NOT IN
(
Select EQNUM
FROM 
EQUIP
)







[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Where are these numbers if they are not in the equip table? Do you have them in a spreadsheet? How many are there? You could handle this in formulas referencing an array if there aren't too many (or too many records in total).

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top