Selecting records NOT marked as "Invalid"

[WaveyDavey]

I have a report which works fine but I need to exclude "Invalid" records from the totals. Records are indicated as Invalid by the existence of a record in the "InvalidDets" table. E.g. a user marks a Referral record as invalid and a record is created on the "InvalidDets" table using the Referral number (from the Referral table).

My question is how do I check for these records NOT being present? The RecordSelectionFormula in the Crystal Report uses a parameter and is as follows: "

if {?ServiceArea} > 0 then {RefInvolveReq.SW_Area} = {?ServiceArea}

". I would like to add something like ..."

 and there is no record on the InvalidDets table where Referral_Number = {RefInvolveReq.Referral_Num}

". Is it possible? If so, how do I achieve it?

I'm using Crystal 8.5 and Visual Basic 6 accessing a SQL 2000 database.

Any advice would be much appreciated.

 

[Naith]

Hey WaveyDavey,

You'd need to bring in some SQL in order to do what you're asking. Anything like a NOT EXISTS, NOT IN, or MINUS between the two tables would get you the recordset without entries in the InvalidDets table.

Naith

 

[WaveyDavey]

Naith,

How would I do that? I know VB and Crystal but have no SQL knowledge or experience.

Dave.

 

[Madawc]

I'd take a different approach. Link the two tables using a 'left outer', so that it includes records without an invalid entry. Then set a suppression formual to exclude those details with an entry in the InvalidDets table, something like
not isnull ({InvalidDets.key})

(If this gets posted twice, sorry. Responses irregular.)

Madawc Williams
East Anglia, Great Britain

 

[Naith]

WaveyDavey,

If you have no SQL resource, then Madawc's solution is certainly another option worth exploring, though the processing will be slower, as it will return all records before then hiding the ones you don't want.

Naith

 

[WaveyDavey]

Thanks guys,

I'll try out Madawc's suggestion and seek some advice on the SQL solution. I'll re-post if still a problem,

Dave.

 

[lbass]

Why not do the left join from the Referral table to the Invalid table and then use the following as the select statement:

isnull({InvalidDets.Number}) and //add the rest of your select statement

This would return only those records that are not invalid.

-LB